# Thread: SUM based on MAX condition (2000)

1. ## SUM based on MAX condition (2000)

I'm using this function =INDEX(G13:G52,MATCH(MAX(B2:B52),B2:B52,0)) to return the most recent entry B2:B52 = Date range andG2:G52 = Amount

This formula works great when there is just one entry in a month; however, when there's more than one entry for a given month, it only returns the most recent entry. What I'd like to do is 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

Thanks for any help provide

2. ## Re: SUM based on MAX condition (2000)

=sum(if(month(b2:B52)=6,g2:G52))

Steve
Or ar you looking for the more generic (confirm with ctrl-shift-enter)
=sum(if(month(b2:B52)=month(max(b2:B52)),g2:G52))

3. ## Re: SUM based on MAX condition (2000)

Thanks Steve,

Hmmm.....both returned a #VALUE! error - not sure why. It does need to be genereric as row(s) will be added each month with new entries.

I am getting closer with this formula, =SUMIF(B2:B52,MAX(B2:B52),G2:G52) , however, for it to work properly, all of the dates must be the same.

4. ## Re: SUM based on MAX condition (2000)

Did you confirm it with ctrl-shift-enter?

It can be made generic, by using Dynamic Names in the formula

If you want to use sumif, you could create an intermediate column which calculates the last day of the month (EOMONTH with analysis toolpack) and then use this column for the sumif...

Steve

5. ## Re: SUM based on MAX condition (2000)

Thanks Steve. I did do ctrl-shift-enter, so I'm not sure why. I think what I'll do is add an additional column, one for statement date and the other for item date, which should work fine. Thanks again for your help.

6. ## Re: SUM based on MAX condition (2000)

The formula Steve suggested should work, see the attached workbook.

7. ## Re: SUM based on MAX condition (2000)

couple other options,

A2:

1-June-05 (or 6/1/2005)

B2:

=EOMONTH(A1,0)

C2:

=SUMIF(\$B\$2:\$B\$52,">="&A2,\$G\$2:\$G\$52)-SUMIF(\$B\$2:\$B\$52,">"&B2,\$G\$2:\$GI\$52)

=SUMPRODUCT(--(MONTH(\$B\$2:\$B\$52)=6),\$G\$2:\$G\$52)

Caveat, no year test....

8. ## Re: SUM based on MAX condition (2000)

In addition to the the ctrl-shift-enter, any text entries in the "date column" will lead to this error since excel can not calculate a month from a text value. Make sure all your dates are numbers and none are just "text that look like a date" or any other strings (including a null string from a formula).

If the dates are calculated and you "hide them" with a null string, you would be better off using a zero instead and then hiding the display of zero via format-cells.

Steve

9. ## Re: SUM based on MAX condition (2000)

Thanks Steve, Hans, et al.

I went back to my date column just to make sure that all the cells were set to the same format, and sure enough, that's were the problem was. After I set all cells to the same format the #VALUE! vanished and the correct SUM'd value appeared.

Thanks for all of your help guys - I appreciate it.

10. ## Re: SUM based on MAX condition (2000)

The formatting should not matter unless you had some of them set to "TEXT". Any of the "number formats" should still calculate correctly...

Steve

11. ## Re: SUM based on MAX condition (2000)

X2:

=EOMONTH(MAX(B2:B52),-1)+1

Y2:

=EOMONTH(X2,0)

Then invoke:

=SUMIF(\$B\$2:\$B\$52,">="&X2,\$G\$2:\$G\$52)-SUMIF(\$B\$2:\$B\$52,">"&Y2,\$G\$2:\$G\$52)

The foregoing is the same suggestion as the one maxfilia10 forwards. The difference is that this is meant to fit to your data and the calculation you want..

#### Posting Permissions

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