Google Answers Logo
View Question
 
Q: EXCEL FORMULA HELP ( Answered 5 out of 5 stars,   0 Comments )
Question  
Subject: EXCEL FORMULA HELP
Category: Computers
Asked by: pachyderm-ga
List Price: $2.00
Posted: 09 May 2006 13:01 PDT
Expires: 08 Jun 2006 13:01 PDT
Question ID: 727034
B1=4/1/06   C1=$500.00
B2=5/1/06   C2=$750.00
B3=6/1/06   C3=$100.00
B4=2/1/06   C4=$550.00
etc         etc

I Want box A2 to show the sum of all dollar amounts in column C that
are before today's date.  I currently have Today() formula in box A1.
Answer  
Subject: Re: EXCEL FORMULA HELP
Answered By: maniac-ga on 09 May 2006 19:03 PDT
Rated:5 out of 5 stars
 
Hello Pachyderm,

There are a few possible solutions for this question, but the simplest
is with an "array formula" (search Excel help with that phrase for an
explanation). The array formula is entered by holding a modifier key
(CTRL on a Windows PC) when entering the formula - the Excel help
explains this more fully. For example:
  copy / paste the formula below into cell A2
  instead of clicking the check mark (or the enter key), use
CTRL-check mark or CTRL-Enter
If done incorrectly, you will get an incorrect result (or #ERR,
depending on Excel version). If done correctly, the formula will
display with braces { } around it and the correct result will appear.

For the values you provided (B1 through B4, C1 through C4), the
formula in A2 should be:
  =SUM(IF(B1:B4<A1,C1:C4,0))
which will appear as
  {=SUM(IF(B1:B4<A1,C1:C4,0))}
after entering as an array formula. Using today's date and the data
you provided, A2 is calculated to be 1250. I tested the formula with a
variety of values of A1 from 3/1/06 to 1/1/07 and it produces correct
results.

To change the number of cells calculated (say - B1 to B10, C1 to C10),
the formula should change to:
  =SUM(IF(B1:B10<A1,C1:C10,0))
As a suggestion, I recommend you leave a blank row after the data you
use for calculation. That way, you can insert rows (for more data)
above the blank row and the formula should update automatically.

Good luck with your work. Please make a clarification request if you
are having difficulty with this solution to your problem.

  --Maniac
pachyderm-ga rated this answer:5 out of 5 stars and gave an additional tip of: $1.00
Thank You.

Comments  
There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy