SUM based on MAX condition (2000)

Topic

New Reply

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

Reply | Quote

Replies

(Edited by sdckapr on 11-Aug-05 11:28. Added additional formula)

How about this array: (confirm with ctrl-shift-enter)
=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))

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Steve

Reply | Quote

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….

Reply | Quote

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..

Reply | Quote