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

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" wrote:

> 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

Thanks a bunch. This worked great.

Amanda

"Toppers" wrote:

> 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" wrote:
>
> > 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