Remember Me?

#1
January 5th 07, 08:13 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 47
Count entries in one column based on values in another column

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!D2687, "<=100")

Since I'm interested in determining my progress within each visit interval
group, I used the following IF function:
=IF((COUNTIF(Data!D2687,"<=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.

#2
January 5th 07, 08:33 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 3,718
Count entries in one column based on values in another column

=IF(Data!D2687<=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!D2687, "<=100")

Since I'm interested in determining my progress within each visit interval
group, I used the following IF function:
=IF((COUNTIF(Data!D2687,"<=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.

#3
January 5th 07, 09:11 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 47
Count entries in one column based on values in another column

Thanks for the prompt response. I'll see if this does the trick.

"Teethless mama" wrote:

=IF(Data!D2687<=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!D2687, "<=100")

Since I'm interested in determining my progress within each visit interval
group, I used the following IF function:
=IF((COUNTIF(Data!D2687,"<=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.

#4
January 5th 07, 09:18 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 47
Count entries in one column based on values in another column

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!D2687<=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!D2687, "<=100")

Since I'm interested in determining my progress within each visit interval
group, I used the following IF function:
=IF((COUNTIF(Data!D2687,"<=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.

#5
January 8th 07, 12:18 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jan 2007 Posts: 24
Count entries in one column based on values in another column

formula
=if(and(n0,n<101),"TEXTa",if(and(n100,n<201),"TE XTb",if(and(n200,n<=300),"TEXTc","Outside Scope")))

n = the 1 to 300 from your added column as mentioned below
set TEXTa,b & c and "Outside Scope" to whatever text you require

From here you can apply countif's to the returned text and this will also
allow you to filter lists by returned text

"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!D2687, "<=100")

Since I'm interested in determining my progress within each visit interval
group, I used the following IF function:
=IF((COUNTIF(Data!D2687,"<=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.

#6
January 9th 07, 01:29 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jan 2007 Posts: 10
Count entries in one column based on values in another column

You may find the use of the SUMPRODUCT function easier for what you
want to achieve/

Ex: data area is named appoint_lap

SUMPRODUCT( 1*(appoint_lap<101)) will give you the total nr of records
of people who have between 0 and 100 between their last appointment

SUMPRODUCT( 1*(appoint_lap100)*(appoint_lap<201)) will give you the
total nr of records of people who have between 100 and 200 between
their last appointment

....etc

You may also investigate database functions such as DCOUNT

Oldersox wrote:
formula
=if(and(n0,n<101),"TEXTa",if(and(n100,n<201),"TE XTb",if(and(n200,n<=300),"TEXTc","Outside Scope")))

n = the 1 to 300 from your added column as mentioned below
set TEXTa,b & c and "Outside Scope" to whatever text you require

From here you can apply countif's to the returned text and this will also
allow you to filter lists by returned text

"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!D2687, "<=100")

Since I'm interested in determining my progress within each visit interval
group, I used the following IF function:
=IF((COUNTIF(Data!D2687,"<=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.

#7
January 10th 07, 10:40 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jan 2007 Posts: 24
Count entries in one column based on values in another column

first on people who are
being monitored more closely" and as such thought the creation of a list to
target patients from the existing data file would be of use. Using a pivot
table will provide calculation of percentages and patient numbers for each
group and or a chart presentation of data.

"bda75" wrote:

You may find the use of the SUMPRODUCT function easier for what you
want to achieve/

Ex: data area is named appoint_lap

SUMPRODUCT( 1*(appoint_lap<101)) will give you the total nr of records
of people who have between 0 and 100 between their last appointment

SUMPRODUCT( 1*(appoint_lap100)*(appoint_lap<201)) will give you the
total nr of records of people who have between 100 and 200 between
their last appointment

....etc

You may also investigate database functions such as DCOUNT

Oldersox wrote:
formula
=if(and(n0,n<101),"TEXTa",if(and(n100,n<201),"TE XTb",if(and(n200,n<=300),"TEXTc","Outside Scope")))

n = the 1 to 300 from your added column as mentioned below
set TEXTa,b & c and "Outside Scope" to whatever text you require

From here you can apply countif's to the returned text and this will also
allow you to filter lists by returned text

"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!D2687, "<=100")

Since I'm interested in determining my progress within each visit interval
group, I used the following IF function:
=IF((COUNTIF(Data!D2687,"<=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.

#8
January 10th 07, 09:29 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 47
Count entries in one column based on values in another column

The pivot table solution seems to be the one that worked. Instead of trying
to use an IF function with embedded COUNTIF functions, I switched to the
DCOUNt function. Here's the syntax:
=DCOUNT(Data!D1:J687,"EF",Results!J23:K24)+DCOUNT( Data!D1:K687,"NYSHC",Results!J23:K24)

I selected the area of the worksheet that contained the column with the
visit interval data, column D, as well as the column containing the data for
the information I was screening, column J with the EF or 'ejection
fraction"--a measure of the heart's pumping capacity. Then, I created a
reference table for the various levels of visit interval, i.e., <=100, etc.
From here, it was a pretty simple procedure.

I now have a new tool to add to my Excel arsenal.

Cheers.

Kurt

"Oldersox" wrote:

first on people who are
being monitored more closely" and as such thought the creation of a list to
target patients from the existing data file would be of use. Using a pivot
table will provide calculation of percentages and patient numbers for each
group and or a chart presentation of data.

"bda75" wrote:

You may find the use of the SUMPRODUCT function easier for what you
want to achieve/

Ex: data area is named appoint_lap

SUMPRODUCT( 1*(appoint_lap<101)) will give you the total nr of records
of people who have between 0 and 100 between their last appointment

SUMPRODUCT( 1*(appoint_lap100)*(appoint_lap<201)) will give you the
total nr of records of people who have between 100 and 200 between
their last appointment

....etc

You may also investigate database functions such as DCOUNT

Oldersox wrote:
formula
=if(and(n0,n<101),"TEXTa",if(and(n100,n<201),"TE XTb",if(and(n200,n<=300),"TEXTc","Outside Scope")))

n = the 1 to 300 from your added column as mentioned below
set TEXTa,b & c and "Outside Scope" to whatever text you require

From here you can apply countif's to the returned text and this will also
allow you to filter lists by returned text

"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!D2687, "<=100")

Since I'm interested in determining my progress within each visit interval
group, I used the following IF function:
=IF((COUNTIF(Data!D2687,"<=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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Sam via OfficeKB.com Excel Worksheet Functions 2 January 2nd 07 11:03 PM David Excel Discussion (Misc queries) 1 January 6th 06 03:56 AM shopaholic Excel Worksheet Functions 1 December 9th 05 01:13 AM [email protected] New Users to Excel 1 May 5th 05 08:11 PM Murph Excel Worksheet Functions 4 February 21st 05 02:44 AM

All times are GMT +1. The time now is 06:55 PM.