Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •