SUMPRODUCT FUNCTION AS COUNTER
December 6, 2011 Leave a comment
COUNTIF and SUMIF are used to count and to sum when a single condition/criterion is involved.
In case of multiple conditions for both counting and summing you need SUMPRODUCT or array formulas.
Often you see SUMPRODUCT/array formulas built up with boolean terms. The following example will explain this
Let Assume the data is laid as below
|
A |
B |
1 |
A |
B |
2 |
A |
B |
3 |
A |
B |
4 |
A |
B |
5 |
B |
A |
6 |
B |
A |
The formula will be
=SUMPRODUCT(–(A1:A6=”A”)*(B1:B6=”B”))
will count records/rows that contain “A” and “B” values.
How it works? It genereates first 2 arrays like:
{TRUE, TRUE,TRUE,TRUE,FALSE,FALSE}*{TRUE,TRUE, TRUE,TRUE,FALSE ,FALSE}
and multiplying (Adding) these two, it gets:
sumproduct({1;1;1;1;0;0})
Multiplying logical values coerce Excel to treat TRUE as 1 and FALSE as 0.
Finally, the numbers in the last array are summed, producing a count.