Countif Function Using "AND"

[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!

 

[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

 

[Teethless mama]

=COUNT(INDEX(SEARCH("Yes",A1:A5)/(B1:B5="Red"),0))

 

[Tyro]

Try this formula

=SUMPRODUCT((A1:A5="yes")*(B1:B5="red"))

Tyro