countif & and

C

Charlie7805

What am I doing wrong? I need to count the number of "Yes" in column W for
all entries with a 72 in column A

=countif(and($a$4:$a$300,72),$w$4:$w$300,"Yes"))
 
D

Dav

Your formula is somewhat strange! What is easiest is to use the
sumproduct function
=SUMPRODUCT(($A$4:$A$300=72)*($W$4:$W$300="Yes"))

Should work. When the condition proves to be true it returns a true
else a false

so when both conditions are true their product (true*true)=1
when only one condition is true (true*false)=0
when non conditions are true (false*false)=0

the sum of these products (as they are all 1) is the same as a count

regards

Dav
 
K

kaza-ki-sthan

You need to have a dummy column say AA, which checks for the 72 and the
"Yes", AA67=IF(AND( A67=72,W67="Yes"),"Y","N").Hide the column AA, if you
want. The do a =COUNTIF($AA$4:$AA$300,"Y").
 
Top