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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: