The formula below doesn't work correctly. It provides the value if FALSE,
i.e., 0.

It should yield a value of 153 for those cases with a visit interval <=100.

"Teethless mama" wrote:

=IF(Data!D2:D687<=100,COUNTIF(Data!I2:I687,"0")+C OUNTIF(Data!I2:I687,"n")+COUNTIF(Data!I2:I687,"d") +COUNTIF(Data!J2:J687, "0"),0)
ctrl+shift+enter, not just enter


"Kurt" wrote:

I'm screening patients for a research project, so in order to efficiently use
my time, I'm using the interval between their last visit and next appointment
as an indicator of how closely they're being monitored by their health care
provider. I have one column containing values that range between 0 and 300.
Since my time is limited, I want to focus my efforts first on people who are
being monitored more closely. Consequently, I am directing my efforts toward
people who have between 0 and 100 between their last appointment, and next.
After I complete this, I'll move on to people who are being monitored less
closely.....I'll then focus on people who have an interval between 100 and
200 days between visits.

I like to keep a running total of my screening progress, so I want to count
the number screened within each visit interval group (i.e., <=100 days,
between 100 and 199 days, and greater that 200 days). I'm able to use the
COUNTIF function to determine how may people fall into each group, but when I
try to embed the COUNTIF statements within an IF statement (one that ends up
doing the COUNTIF only for those cases falling within a specific visit
interval group), I get results that don't make sense. My goal is to be able
to produce a chart that looks like this:

n % n screened % screened
Number cases w/visit interval <=100 days: 240 34.99 153 63.75

Number cases w/visit interval between 100 and 200 days:
n % n screened % screened 172 25.07
Number cases w/visit interval 200 days:
n % n
screened % screened
268 39.07

Number cases w/no visit interval:
n %
n screened % screened
6 0.87

Total cases: 686

To calculate the number of cases with a visit interval <=100, I used this
function: =COUNTIF(Data!D2:D687, "<=100")

Since I'm interested in determining my progress within each visit interval
group, I used the following IF function:
=IF((COUNTIF(Data!D2:D687,"<=100")),COUNTIF(Data!I 2:I687,"0")+COUNTIF(Data!I2:I687,"n")+COUNTIF(Dat a!I2:I687,"d")+COUNTIF(Data!J2:J687, "0"),0)

As a test, I located an entry with a visit interval greater than 100,
entered a value, and the cell containing the funtion intended to only count
entries for cases within the <=100 day visit interval increased. I am
stumped.

Thanks in advance for the suggestions.