Countif Function Using "AND"

K

Kika

How can I use the Countif Function to count every time the value in one
column/range equals criteria-1 AND the value in a second column/range equals
criteria-2. In the example below, I want to count every time that column A
equals "Yes" AND column B equals "Red." In this example I should get a count
of 2.

A B
1 Yes Red
2 Yes Blue
3 No Red
4 Yes Red
5 Yes Green

I used the following formula, but the result is "true". I also don't
understand why I'm getting a result of "true" to a Countif formula.

=AND(COUNTIF(A1:A5,"Yes"),COUNTIF(B1:B5,"Red"))

Thanks for any help that anyone can provide!
 
C

carlo

How can I use the Countif Function to count every time the value in one
column/range equals criteria-1 AND the value in a second column/range equals
criteria-2.  In the example below, I want to count every time that column A
equals "Yes" AND column B equals "Red."  In this example I should get a count
of 2.

          A          B
  1     Yes        Red
  2     Yes        Blue
  3      No        Red
  4      Yes       Red
  5      Yes       Green  

I used the following formula, but the result is "true".  I also don't
understand why I'm getting a result of "true" to a Countif formula.      

     =AND(COUNTIF(A1:A5,"Yes"),COUNTIF(B1:B5,"Red"))

Thanks for any help that anyone can provide!

You get the result true, because AND can only return a boolean.

you could try this:

=SUMPRODUCT((A1:A5="Yes")*(B1:B5="Red"))

hth
Carlo
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top