"countif" and "and" condition

[laingram]

I have an table with cases eligible to be enrolled in a study on a worksheet
called LOG. There are 3 columns to indicate whether or not a case is eligible
(Q), enrolled (R) or refused (S). The values are either YES or NO.

I am trying to automate my counts for the following criteria (functions are
also listed):
1) **Eligible**
i.e. Eligible=YES
=COUNTIF(LOG!Q3:Q1000,"YES")
2) **Enrolled**
i.e. Eligible=YES and Enrolled=YES
=IF(LOG!Q3:Q1000="YES",COUNTIF(LOG!R3:R1000,"YES"))
3) **Not Enrolled, Other**
i.e. Eligible=YES and Enrolled=NO and Refused=NO
4) **Not Enrolled, Refused**
i.e. Eligible=YES and Enrolled=NO and Refused=YES
5) **Not Eligible**
i.e. Eligible=NO
=COUNTIF(LOG!Q3:Q1000,"NO")

I am having problems writing the formula for the "Not Enrolled, Other" and
"Not Enrolled, Refused" categories. I was hoping to use the AND function, but
it doesn't seem to be working.

This is what I was working on:
=IF(AND(LOG!Q3:Q1000="YES",LOG!S3:S1000="NO"),COUNTIF(LOG!R3:R1000,"YES"))

Thanks in advance for any suggestions,
Amanda

 

[Toppers]

use SUMPRODUCT

=SUMPRODUCT(--(Q3:Q1000=condition1),--(R3:R1000=condition2),--(s3:S1000=condition3))

Remove conditions as required

=SUMPRODUCT(--(Q3:Q1000="YES"))

=SUMPRODUCT(--(Q3:Q1000="YES"),--(R3:R1000="YES"))

etc

HTH

 

[laingram]

Thanks a bunch. This worked great.

Amanda

use SUMPRODUCT

=SUMPRODUCT(--(Q3:Q1000=condition1),--(R3:R1000=condition2),--(s3:S1000=condition3))

Remove conditions as required

=SUMPRODUCT(--(Q3:Q1000="YES"))

=SUMPRODUCT(--(Q3:Q1000="YES"),--(R3:R1000="YES"))

etc

HTH