sumproduct - multiple conditions

[Marcotte A]

I want to get the sum of all transactions in my table of type "RI" or "II" or
"AI". I am also filtering for other conditions (warehouse and merchandise
class), but these conditions have only one option for each cell in my
spreadsheet. In other words, for each warehouse and merchandise class, I
need to show the sum total of those three types of transactions. I could do
something like this:

=SUMPRODUCT(--(<wh column>=<wh>),--(<type column>="RI"),--(<merch
column>=<merch>),'<amt column>)+SUMPRODUCT(--(<wh column>=<wh>),--(<type
column>="II"),--(<merch column>=<merch>),'<amt column>)+SUMPRODUCT(--(<wh
column>=<wh>),--(<type column>="AI"),--(<merch column>=<merch>),'<amt column>)

where everything in "<>" is a cell or array reference. That is really long
and messy.

Is there a way to put an OR statement inside the SUMPRODUCT? Something
along the lines of changing "--(<type column>="RI")" to "--(OR(<type
column>="RI",<type column>="II",<type column>="AI"))" which doesn't work.

 

[Bob Phillips]

=SUMPRODUCT((<wh column>=<wh>)*(<type column>={"RI",II","A"})*(<merch
column>=<merch>)*(<amt column>))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Peo Sjoblom]

Example

=SUMPRODUCT(--(wh_column>=I1),--((type_column=J1)+(type_column=K1)+(type_column=L1)>0),amt_column)

where the type_column can be either J1, K1 or L1 and where wh_column is
greater than or equal to I1 then sum those values in amt_column

 

[Marcotte A]

Thank you. This works for me.

Follow up question. Is the ">0" necessary? The double unary parses the
array as a boolean, so any non zero value gets evaluated as TRUE (1), correct?

 

[Bob Phillips]

No, it is not necessary here. It is not the double unary here that does the
coercing, but the +, TRUE+FALSE+FALSE=1, FALSE+FALSE+TRUE=1, etc., and
FALSE+FALSE+FALSE=0. In fact this double unary is superfluous, all you need
is

=SUMPRODUCT(--(wh_column>=I1),((type_column=J1)+(type_column=K1)+(type_column=L1)),amt_column)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Just to add, what I described was because of that particular test, where
only one column was being tested for 3 different values, that is only one
could be true.If you were doing an OR on separate columns, where more than
one condition could be met, the >0 test is useful so as to avoid double
counting

=SUMPRODUCT(--(wh_column>=I1),--(((type_column=J1)+(type2_column=K1)+(type_column=L1))>0),amt_column)

and you will notice you need the double unary again, because the >0 3 item
test will resolve to TRUE or FALSE.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

As a further aside, you can also handle the separate column ORs using SIGN

=SUMPRODUCT(--(wh_column>=I1),SIGN((type_column=J1)+(type2_column=K1)+(type_column=L1)),amt_column)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Peo Sjoblom]

Itis not needed this time since you are looking in the same range so easiest
is to use an array hardcoded. However I use it for consistency, if indeed
you were using more than one range in your OR using the same criteria it is
needed

x x
b x
c x
x x
x x
c x
a x
x x
c x
a x
x x

the above is in A2:B10, assume we want to count x using OR, basically saying
=OR(A2="x",B2="x")

then copied down and counted would give 11, if you use


(A2:A10="x")+(B2:B10="x")


it will return 16

whereas

--((A2:A12="x")+(B2:B12="x")>0)

will return 11

so to me I know that if I use this it will work in all cases