SUMPRODUCT FUNCTION AS COUNTER

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.

Advertisements