Google Answers Logo
View Question
Q: EXCEL FORMULA HELP ( Answered 5 out of 5 stars,   0 Comments )
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.
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:
which will appear as
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

To change the number of cells calculated (say - B1 to B10, C1 to C10),
the formula should change to:
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.

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

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 with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

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