Sumif with criteria list

[Guest]

I have a row of data that I want to sum if the corresponding collumns are one
of several departments. I can't figure out how to use multiple criteria with
the sumif function and the data is not in typical DB format so I can't use
the DSUM function. Does anyone know how to solve this problem?

Thanks in advance,

Brian

Sample
Company A Company B Company C Company D
Assets 15 5 20 25

Question: What are the assets for companies A and B.
My actual situation could have 15 different search requirements.

 

[KL]

Hi Brian,

a couple of options:

=SUMPRODUCT(SUMIF(B1:E1,{"Company A","Company B"},B2:E2))

=SUMPRODUCT((B1:E1={"Company A";"Company B"})*B2:E2)

The fixed arrays can be replaced by range references: in the 2nd formula the
range must be vertical (or horizontal with TRANSPOSE function).

Regard,
KL

 

[Domenic]

Try...

=SUMPRODUCT(--(ISNUMBER(MATCH($B$1:$E$1,{"Company A","Company
B"},0))),B2:E2)

OR

=SUMPRODUCT(--(ISNUMBER(MATCH($B$1:$E$1,$G$1:$G$2,0))),B2:E2)

....where G1:G2 contains your company names.

Hope this helps!

 

[Guest]

Thanks KL,
That solved my problem. I was actually able to just enclose the SUMIF
formula I alread had with the SUMPRODUCT formula. Is the reason why the
SUMPRODUCT formula works because it is able to handle arrays where as the
SUMIF is not?

 

[KL]

Well, not exactly. SUMIF does return an array if the second argument is an
array. The question is how do you sum the members of that array. You can, of
course, use the SUM function, but will have to confirm it by
Ctrl+Shift+Enter, whereas SUMPRODUCT doesn't need to be array entered to
handle arrays.

KL

 

[Guest]

Makes Sense...thanks for the help

Brian