multiple if conditions - use sumif/sumProduct?

[macs]

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))

 

[RagDyer]

First of all, your formula is missing a pair of parens:

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

Second, since it's an array formula, you'll need to enter it with CSE:
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

You didn't post your attempt using Sumproduct (NO CSE necessary).

Here's one that will work:

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

 

[Joel]

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")*(E1189:$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

 

[smartin]

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.

 

[smartin]

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.

 

[T. Valko]

One way...

Normally entered:

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