Countif question

  • Thread starter Thread starter Cass
  • Start date Start date
C

Cass

Can someone help me with a countif formula please.?
I am trying to count text information in 2 columns to return a
numerical count.
For example if b5:b25 contains "G" and d5:d25 contains "p" how many
rows contain g and P in the same row?

eg:

f G h P
f G h
f G h P
f h P
f G P

The answer here would be 3.

Any help would be happily appreciated.

:confused:
 
Hello Cass,

COUNTIF only works with one variable. Since you're working with two
variables, you need a different approach.

Say your data is in A1:D5, then the following array formula will give the
required result:
=SUM(IF((B1:B5="G")*(D1:D5="P"),1,0))
After typing/copying this in, you press Ctrl-Shift-Enter, instead of just
enter, to turn it into an array formula. Then it should look like:
{=SUM(IF((B1:B5="G")*(D1:D5="P"),1,0))}
What this does is to give every row on which both G and P appear a value of
1, and then sums the count of 1s.

Cheers
 
Cass

|Another alternative is to use SUMPRODUCT()

=SUMPRODUCT((B5:B25="G")*(D5:D25="P"))

Andy.
 
Back
Top