# Thread: SUM based on MAX Condition - Round 2 (2000)

1. ## SUM based on MAX Condition - Round 2 (2000)

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

2. ## Re: SUM based on MAX Condition - Round 2 (2000)

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)

3. ## Re: SUM based on MAX Condition - Round 2 (2000)

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.

4. ## Re: SUM based on MAX Condition - Round 2 (2000)

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(DA TE)=MONTH(MAX(DATE)))*AMOUNT)

5. ## Re: SUM based on MAX Condition - Round 2 (2000)

No problem and thank you. Both worked perfectly.

Scott

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•