Counting number of rows based on mutiple criteria

[Matt Brewer]

Does anyone know how to count the number of rows based on mutiple criteria on
other columns? For example I want to count the number row that meet the
criteria of "Yes" in Col 2 and "Yes" in Col 3. The result would be 2 in the
example below. I tried different combinations of Vlookup, Countif and Sumif
and could not come up with anything that worked.

Col 1 Col2 Col3
A Yes No
B Yes Yes
C No No
D No Yes
E Yes Yes

Your help is greatly appreciated!

 

[Mike H]

Hi,

=SUMPRODUCT((B1:B10="Yes")*(C1:C10="Yes"))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Sninkle]

You can use DCOUNTA. It's a little different than using the other functions.
Please see the example below. DCOUNTA makes it easy to look up different
criteria.

Ex.
This would be the data
Apples(A1) Oranges(B1)
yes yes
yes no
no yes

Now we create a lookup table. Either on the same tab or another enter the
column headers and any data entered under those headers will be the criteria
for the function.

Apples(D1) Oranges(E1)
Yes Yes

Now enter the DCOUNTA formula into another cell
=DCOUNTA(A1:B4,"Apples",D1:E2)

This will bring a count of 1. If the "yes" under Oranges is deleted the
result will be 2.

I hope this helps.