|
Back to Excel Tips Index
(Count)
Synt.
=SUMPRODUCT(--(Rng_1=Val1), --(Rng_2=Val2))
ex.
=SUMPRODUCT(--(A2:A100 = “abcd”), --(B2:B100 = 1234))
(Sum)
Synt.
=SUMPRODUCT(--(Rng_1=Val1), --(Rng_2=Val2), Rng_to_Sum)
ex.
=SUMPRODUCT(--(A2:A100 = “abcd”), --(B2:B100 = 1234),
C2:C100)
Important: All ranges must be of equal size. Also, cannot use whole-column ranges such as
A:A.
Note: Multi-Conditional Count and Sum can be accomplished with a
Pivot Table but for dynamic spreadsheet design these formulas are excellent (but
memory hogs.) Also, up to 30 conditions when counting, 29 conditions when
summing (plus one range to sum.)
Back to Excel Tips Index |
Eventually everyone asks,
"What do the "--" mean?
The double negative turns
TRUE/FALSE into 1/0 respectively by performing a mathematical operation
(multiplying by -(-1)) on the result of the condition. As an
experiment, type =TRUE in a cell. The result is TRUE. Now,
type =--(TRUE). The result is 1. Now try it with FALSE to see
what you get.
|
