CountIf across multiple columns?

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

Hi - I need to count the number of rows that have particular values in 2
columns.

For example, for rows 2 through 50, I need to count how many rows have a Y
in Col B and an N in Col D.
(If Col B = Y and Col D = N then count the row).

....Col B .... Col D

2 Y N
3 N N
4 Y Y
5 N Y
6 Y N
......

The formula needs to give me an answer of 2 given the rows above.

Any way to do this in a single formula?

Pete Derkowski
 
Excellent! Thanks! I saw the SUMPRODUCT formula in a previous reply to
someone else's quesiton, but it just didn't seem like it would count up the
columns in the way I needed. Guess the word 'SUM' threw me.

Thanks again,

Pete
 
The formula exploits the fact that Excel interprets TRUE as 1 and FALSE
as 0. Take the formula below:-

=SUMPRODUCT((A9:A20=A1)*(B9:B20="A")*(C9:C20))

This sets up an array that gives you something that looks like this
(depending on the variables of course):-

A B C
9 TRUE * FALSE * 3
10 FALSE * FALSE * 4
11 TRUE * TRUE * 2
12 TRUE * TRUE * 1
13 TRUE * FALSE * 4
14 TRUE * TRUE * 3
15 TRUE * TRUE * 2
16 FALSE * TRUE * 8
17 TRUE * TRUE * 6
18 TRUE * TRUE * 8
19 TRUE * TRUE * 7
20 TRUE * TRUE * 6

Which because TRUE=1 and FALSE=0, is interpreted as:-

A B C
9 1 * 0 * 3 = 0
10 0 * 0 * 4 = 0
11 1 * 1 * 2 = 2
12 1 * 1 * 1 = 1
13 1 * 0 * 4 = 0
14 1 * 1 * 3 = 3
15 1 * 1 * 2 = 2
16 0 * 1 * 8 = 0
17 1 * 1 * 6 = 6
18 1 * 1 * 8 = 8
19 1 * 1 * 7 = 7
20 1 * 1 * 6 = 6
-------------
35

and the SUM bit just adds up all the end values of the products

If you exclude the last part of the formula, so that it becomes:-

=SUMPRODUCT((A9:A20=A1)*(B9:B20="A"))

then what you end up with is a sum of a bunch of TRUE/FALSE values depending on whether or not the
criteria has been met on that row, and this is the same as counting the number of records that
meet your criteria. Imagine the above tables without Column C, and the last one would look like
the following:-

A B
9 1 * 0 = 0
10 0 * 0 = 0
11 1 * 1 = 1
12 1 * 1 = 1
13 1 * 0 = 0
14 1 * 1 = 1
15 1 * 1 = 1
16 0 * 1 = 0
17 1 * 1 = 1
18 1 * 1 = 1
19 1 * 1 = 1
20 1 * 1 = 1
 
Back
Top