Formula(s) to compute sums through last complete month

Posted by Dwight on June 08, 2001 5:06 AM

Column A represents dollar amounts received throughout an entire year. Column B represents dates associated with receipt of the dollars. I need to report month to date and year to date totals, both of which will run only through the last completed month. For instance, if $100 of revenue was received 1/15/01, $200 on 2/15/01, and $400 on 3/15/01 (assume this is current date), the year to date total in cell C1 would be $300 and the month to date total in cell D1 would be $200.

Any help would be appreciated.

Posted by Aladin Akyurek on June 08, 2001 3:55 PM

Dwight

Maybe there are other means to do what you require. For time being, you can use the following I guess.

I'll assume your sample data to be in A1:B3.

In C1 enter: MTD
In C2 enter: Jan-01 [ copy down up to the row where you get Dec-01 ]
In D2 array-enter: =SUM((MONTH(B1:B3)&LT;=MONTH(C2))*(A1:A3)) [ copy down as far as needed ]

Note. In order to array-enter a formula, hit CONTROL+SHIFT+ENTER at the same time (not just ENTER).

In E1 enter: YTD
In E2 array-enter: =SUM((YEAR(B1:B3)=YEAR($F$1))*(B1:B3 &LT; $F$1)*(A1:A3))
In F1 enter: 3/15/01 [ from your example ]

Aladin

=============

Posted by Dwight on June 11, 2001 8:04 AM

Sorry, Aladin, but I probably gave you insufficient data

I think your solution presupposes that there will be one data entry per month. The actual may have numerous individual entries per month in varying amounts and number of entries. Instead of A1:B3 my actual range will probably need to be something like A1:B200. If you're interested in taking another stab at it, I would be glad to e-mail you the actual spreadsheet which might help hone in o the problem. My e-mail is dwight.hamilton@53.com.
Thanks