SUM based on MAX Condition – Round 2 (2000)

Topic

New Reply

Last year I posted a question and got an answer on being able to SUM all of the entries for a month, e.g. 6/5/05 = $100, 6/15/05 = $50.00, 6/30/05 = $25.00, 6/30/05 = ($50.00) which would return $125.00; however, what I didn’t take into consideration, nor did I make clear in my earlier post that I needed to test not only the month, but also the year, e.g. entries from June 05 should not be included with entries from June 06 to make the calculation.

Here is the formula I’m using now: {SUM(IF(MONTH(DATE)=MONHT(MAX(DATE)),AMOUNT))}

DATE is a defined name and = $G$13:$G:$53
AMOUNT is a defined name and = $B$13:$B:$53

Since it’s been a year since we’ve been inputting monthly data, the issue has only just surfaced.

Thanks!

Scott

Reply | Quote

Replies

Try

=SUM(IF(AND(YEAR(DATE)=YEAR(MAX(DATE)),MONTH(DATE)=MONTH(MAX(DATE))),AMOUNT))

(again, as an array formula, i.e. confirm with Ctrl+Shift+Enter)

Reply | Quote

Thanks Hans. That didn’t seem to work. Only $0.00 was returned. I tried it in a couple of different worksheets, with the same result.

Reply | Quote

Sorry, I hadn’t tested it. Try this array formula

=SUM(IF((YEAR(DATE)=YEAR(MAX(DATE)))*(MONTH(DATE)=MONTH(MAX(DATE))),AMOUNT))

or this “normal” (non-array) formula

=SUMPRODUCT((YEAR(DATE)=YEAR(MAX(DATE)))*(MONTH(DATE)=MONTH(MAX(DATE)))*AMOUNT)

Reply | Quote

No problem and thank you. Both worked perfectly.

Scott

Reply | Quote