sumif with multiple criteria..

[Reed]

I need to sum certain cells in a column assuming multiple
criteria are true. A sumif funtion would work for this
if only one criteria needed to be tested. However, I
can't think of a way to do a sumif with multiple
criteria. Any ideas would be greatly appreciated.

Thanks,
Reed

 

[Anon]

I need to sum certain cells in a column assuming multiple
criteria are true. A sumif funtion would work for this
if only one criteria needed to be tested. However, I
can't think of a way to do a sumif with multiple
criteria. Any ideas would be greatly appreciated.

Thanks,
Reed

Use SUMPRODUCT. You can put in as many criteria as you wish. For example
=SUMPRODUCT( (A1:A100="ABC") * (B1:B100>15) * (C1:C100=$E$1) * D1100)

 

[reed]

-----Original Message-----


Use SUMPRODUCT. You can put in as many criteria as you wish. For example
=SUMPRODUCT( (A1:A100="ABC") * (B1:B100>15) * (C1:C100=$E$1) * D1100)

I tried doing this, but it doesn't seem to work, I get a
a #NUM error. Furthermore, maybe you could give me some
insight into this technique. Does the * here simply mean
multiplication, or does it mean something else?

 

[Alan Beban]

=SUM(SUMIF(A1:A10,{1stValue,2ndValue,3rdValue},B1:B10))

Alan Beban

 

[Mathew P Bennett]

Hi Thread.
Alan, this =sum(sumif(... formula looks interesting, how many conditions 1st
Value,2nd Value etc. can one use?
Cheers
Mathew

 

[Mathew P Bennett]

Hi Again Thread
Cheers Alan, this array formula has helped me with a completely different
problem from original post from Reed.
Cheers
Mathew

 

[Alan Beban]

I don't know, I got bored after 10. Once it passed 7, the next limit I
would guess at would be 30.

Why don't you report back after you've checked it out

Alan Beban