Sum w/ Multiple Criteria

  • Thread starter Thread starter Kevin M
  • Start date Start date
K

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
 
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.
 
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
 
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
 
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)
 
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?
 
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
 
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
 
Back
Top