Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    SUMIF with Multiple Criteria (97)

    Hi Everyone!

    So I'm reading my Excel Formulas book by John Walkenbach and it states "The SUMIF function does not work with multiple criteria, you need to resort to using an array formula"

    So this means I need help.

    Currently I am using a SUMIF like this:

    =SUMIF(Data!$D$3:$D$598,"Doe",Data!$J$3:$J$598)

    Here is my data

    Column D = Names
    Column J = Numbers
    Column L = Dates (or the word complete)
    Column M = Dates (or the word complete)

    What I want is for a fomula that says this:

    SUM the values in J if D = Doe, L contains a value, and M contains no value.

    How do I do this...

    Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SUMIF with Multiple Criteria (97)

    Try the following formula:

    =SUM((D3598="Doe")*NOT(ISBLANK(L3:L598))*ISBLANK(M3:M598)* J3:J598)

    It must be entered as an array formula; that is, confirm with Ctrl+Shift+Enter instead of just Enter. You'll see brackets around the formula, but you mustn't type those.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: SUMIF with Multiple Criteria (97)

    I did it all on my own!!!

    I am so excited!!!!!! Here is what I did:

    {=SUM(IF(Data!$D$3:$D$598=Scorecard!B31,IF(Data!$L $3:$L$598>0,IF(Data!$M$3:$M$598="",Data!$J$3:$J$59 8,""))))}

    Hans - Is using the ISBLANK better than saying > 0 ?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SUMIF with Multiple Criteria (97)

    Congratulations! <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

    For dates it doesn't matter. If you had a column with numbers that might be positive or negative or blank, >0 wouldn't return the correct answer of course.

  5. #5
    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: SUMIF with Multiple Criteria (97)

    Instead of
    =SUM((D3598="Doe")*NOT(ISBLANK(L3:L598))*ISBLANK(M3:M598)* J3:J598)
    I prefer the longer:
    =SUM(if((D3598="Doe")*NOT(ISBLANK(L3:L598))*ISBLANK(M3:M598), J3:J598))
    which also allows replacing the SUM with Average, MIN, MAX, STDEV, etc to get statistical details

    Also (as another tip) for ORs: replace the "*" with "+"
    =SUM(if((D3598="Doe")+NOT(ISBLANK(L3:L598))+ISBLANK(M3:M598), J3:J598))
    and this also allows replacing the SUM with Average, MIN, MAX, STDEV, etc to get statistical details

    Again these are ARRAY function use ctrl-shift-enter not enter to cnfirm.
    Steve

Posting Permissions

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