Thread: SUMIF with Multiple Criteria (97)

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

=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
•