multiple if conditions - use sumif/sumProduct?
 

I have a situation where I need to sum a column based on several conditions
in different columns. For every "exp" item (column M), I must only sum
(column F) those that contain a "D" or "M" in column E. Below is the
forumula I used (and there was a sum product I used), but they either return
#VALUE or not the correct number. I've verified the data and used something
similar for just 1 argument and that has worked, but using more than 1 is
throwing me off. I may have to add more items from column E in the future,
so I need to know how to encompass more. Help!

=SUM(IF(($M1189:$M1316="exp")*($E1189:$E1316="D")+ ($E1189:$E1316="M"),$F1189:$F1316))

multiple if conditions - use sumif/sumProduct?
 

rirst you shouldn't have a plus sign in the formula it should be another
asterisk.

from
=SUM(IF(($M1189:$M1316="exp")*($E1189:$E1316="D")+ ($E1189:$E1316="M"),$F1189:$F1316))
to
=SUM(IF(($M1189:$M1316="exp")*($E1189:$E1316="D")* $E1189:$E1316="M"),$F1189:$F1316))


Second I don't think you need the IF. Te equal expressions will return a 1
for true and 0 for false.

from
=SUM(IF(($M1189:$M1316="exp")*($E1189:$E1316="D")* $E1189:$E1316="M"),$F1189:$F1316))

to
=SUM($M1189:$M1316="exp")*($E1189:$E1316="D")*(E11 89:$E1316="M")*(1189:$F1316))


Thrd this is an arry formula. You need to enter the formula by typing
shift-Ctl-enter which wil add curly brackets around the formula in the F(x)
box
"macs" wrote:

I have a situation where I need to sum a column based on several conditions
in different columns. For every "exp" item (column M), I must only sum
(column F) those that contain a "D" or "M" in column E. Below is the
forumula I used (and there was a sum product I used), but they either return
#VALUE or not the correct number. I've verified the data and used something
similar for just 1 argument and that has worked, but using more than 1 is
throwing me off. I may have to add more items from column E in the future,
so I need to know how to encompass more. Help!

=SUM(IF(($M1189:$M1316="exp")*($E1189:$E1316="D")+ ($E1189:$E1316="M"),$F1189:$F1316))

multiple if conditions - use sumif/sumProduct?
 

macs wrote:
I have a situation where I need to sum a column based on several conditions
in different columns. For every "exp" item (column M), I must only sum
(column F) those that contain a "D" or "M" in column E. Below is the
forumula I used (and there was a sum product I used), but they either return
#VALUE or not the correct number. I've verified the data and used something
similar for just 1 argument and that has worked, but using more than 1 is
throwing me off. I may have to add more items from column E in the future,
so I need to know how to encompass more. Help!

=SUM(IF(($M1189:$M1316="exp")*($E1189:$E1316="D")+ ($E1189:$E1316="M"),$F1189:$F1316))

A few things to think about...

First, check the logic in your IF:

X * Y + Z [X AND Y, OR Z]

is not the same as

X * (Y + Z) [X AND (Y OR Z)]

Second, the SUM(IF([multiple conditions])) construction requires array
entry via Ctrl+Shift+Enter. Else, incorrect results.

Third, a short list of alternate/OR criteria (Y OR Z) can be abbreviated
to something like:

={"D","M"}

With all that, here are a new formulas to try (don't forget to press
Ctrl+Shift+Enter to commit them):

=SUM(IF(($M1189:$M1316="exp")*(($E1189:$E1316="D") +($E1189:$E1316="M")),$F1189:$F1316))

=SUM(IF(($M1189:$M1316="exp")*($E1189:$E1316={"D", "M"),$F1189:$F1316))

or you can use SUMPRODUCT, normally entered:

=SUMPRODUCT(($M1189:$M1316="exp")*($E1189:$E1316={ "D","M"})*$F1189:$F1316)

Fourth, if there are errors in your data, some of the above formulas
will sometimes ignore the errors; at other times the error will
propagate to the result.

multiple if conditions - use sumif/sumProduct?
 

Joel wrote:
rirst you shouldn't have a plus sign in the formula it should be another
asterisk.

from
=SUM(IF(($M1189:$M1316="exp")*($E1189:$E1316="D")+ ($E1189:$E1316="M"),$F1189:$F1316))
to
=SUM(IF(($M1189:$M1316="exp")*($E1189:$E1316="D")* $E1189:$E1316="M"),$F1189:$F1316))

Sure?

($E1189:$E1316="D")*$E1189:$E1316="M"

imposes mutually exclusive conditions that will always be FALSE. The OP
stated column E could contain "D" OR "M".

Nevermind the unmatched parens.

multiple if conditions - use sumif/sumProduct?
 

One way...

Normally entered:

=SUMPRODUCT(--($M1189:$M1316="exp"),--(ISNUMBER(MATCH($E1189:$E1316,{"D","M"},0))),$F118 9:$F1316)

--
Biff
Microsoft Excel MVP


"macs" wrote in message
...
I have a situation where I need to sum a column based on several conditions
in different columns. For every "exp" item (column M), I must only sum
(column F) those that contain a "D" or "M" in column E. Below is the
forumula I used (and there was a sum product I used), but they either
return
#VALUE or not the correct number. I've verified the data and used
something
similar for just 1 argument and that has worked, but using more than 1 is
throwing me off. I may have to add more items from column E in the
future,
so I need to know how to encompass more. Help!

=SUM(IF(($M1189:$M1316="exp")*($E1189:$E1316="D")+ ($E1189:$E1316="M"),$F1189:$F1316))