SUMIF with Multiple Criteria (97)

Topic

New Reply

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!

Reply | Quote

Replies

Try the following formula:

=SUM((D3:D598=”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.

Reply | Quote

Instead of
=SUM((D3:D598=”Doe”)*NOT(ISBLANK(L3:L598))*ISBLANK(M3:M598)*J3:J598)
I prefer the longer:
=SUM(if((D3:D598=”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((D3:D598=”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

Reply | Quote

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$598,””))))}

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

Reply | Quote

Congratulations!

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.

Reply | Quote