Countif Function Using "AND"
 

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!

Countif Function Using "AND"
 

On Jan 31, 11:10*am, Kika wrote:
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

Countif Function Using "AND"
 

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


"Kika" wrote:

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!

Countif Function Using "AND"
 

Try this formula

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

Tyro

"Kika" wrote in message
...
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!