Sum w/ Multiple Criteria

[Kevin M]

Hello everyone, I know it's possible to do a SUMIF based
on two criteria

ie:{=SUM(($A$1:$A$20="Miller")*($B$1:$B$20="Jan")
*$C$3:$C$20)}

Is it possible to keep going with a third, fourth, or
fifth criteria? I don't see the likelyhood that someone
would need more than two or three criterion, but hey, you
never know.

Anyway, TIA
Kevin M

 

[Dave R.]

Yes- you can keep adding criteria to it just adding
*(conditon3)*(condition4)..

Incidentally I think you mean C1:C20 because C3:C20 wouldn't work.

 

[firefytr]

hi Kevin, yes. but i would personally use the SUMPRODUCT function fo
that instead of a SUM array, as it's nearly twice as fast and non-arra
entered

 

[Kevin M]

Hey thanks Dave, it is C1, i pulled an example out of one
of my sheets and tried to clean it up but forgot one..
oops.

Thanks again.
Kevin M

 

[Peo Sjoblom]

Do you have any stats to back up that it is twice as fast?

 

[Dave R.]

As Firefytr says, sumproduct maybe the preferred method. 2x as fast, I don't
know about that but it is faster.

For your example it would be;

=SUMPRODUCT((A1:A20="Miller")*(B1:B20="Jan"),C1:C20)

and doesn't have to be array entered.

Note; if you want to do a one-condition count/sum you need to change the
true/false expression (a1:a20="miller") into a number, the most common way
is to put a -- in front of the parens which turn true/false to 1/0.

=SUMPRODUCT(--(A1:A20="Miller"),C1:C20)

 

[Peo Sjoblom]

How do you know it's faster? I use it because I don't have to array enter it
but I doubt it is much faster?

 

[Dave R.]

Here's one link. To be honest, I was actually mis-remembering, I was
thinking of the lengthy discussion of using "--" being faster than * (if I'm
remembering that correctly) in sumproduct. But, in some worksheets I've done
at work, when I use sumproduct they seem to calculate a bit faster. I had
some big spreadsheets. Never did any actual time trails, but someone in the
thread below did.

http://tinyurl.com/35v9d

 

[firefytr]

yes, i apologize Peo, i remembered it being timed and being faster.
although was off on my estimation. besides the efficiency, it's jus
altogether easier to use and troubleshoot

 

[Peo Sjoblom]

Ok, 10%, that's a far cry from 100% faster as the other poster claimed.

 

[Peo Sjoblom]

I agree with that. I use it whenever I can..

 

[Harlan Grove]

Do you have any stats to back up that it is twice as fast? ...

...

Here's the only data I'm aware of.

http://www.google.com/[email protected]

SUMPRODUCT seems faster, but nowhere near twice as fast.