Results 1 to 11 of 11
  1. #1
    Star Lounger
    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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 11-Aug-05 11:28. Added additional formula)</P>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))

  3. #3
    Star Lounger
    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.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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 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. #5
    Star Lounger
    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 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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SUM based on MAX condition (2000)

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

  7. #7
    Star Lounger
    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:

    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. #8
    WS Lounge VIP sdckapr's Avatar
    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 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. #9
    Star Lounger
    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.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    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

  11. #11
    3 Star Lounger
    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

Posting Permissions

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