# 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 ]