Results 1 to 11 of 11

20050811, 14:02 #1
 Join Date
 Feb 2003
 Posts
 60
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20050811, 14:28 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: SUM based on MAX condition (2000)
<P ID="edit" class=small>(Edited by sdckapr on 11Aug05 11:28. Added additional formula)</P>How about this array: (confirm with ctrlshiftenter)
=sum(if(month(b2:B52)=6,g2:G52))
Steve
Or ar you looking for the more generic (confirm with ctrlshiftenter)
=sum(if(month(b2:B52)=month(max(b2:B52)),g2:G52))

20050811, 15:39 #3
 Join Date
 Feb 2003
 Posts
 60
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20050811, 17:03 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: SUM based on MAX condition (2000)
Did you confirm it with ctrlshiftenter?
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

20050811, 17:14 #5
 Join Date
 Feb 2003
 Posts
 60
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUM based on MAX condition (2000)
Thanks Steve. I did do ctrlshiftenter, 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.

20050811, 18:39 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: SUM based on MAX condition (2000)
The formula Steve suggested should work, see the attached workbook.

20050811, 22:35 #7
 Join Date
 Feb 2003
 Posts
 89
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUM based on MAX condition (2000)
couple other options,
A2:
1June05 (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....

20050811, 22:51 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: SUM based on MAX condition (2000)
In addition to the the ctrlshiftenter, 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 formatcells.
Steve

20050812, 10:03 #9
 Join Date
 Feb 2003
 Posts
 60
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20050812, 10:11 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20050813, 12:09 #11
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
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..Microsoft MVP  Excel