Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default Count entries in one column based on values in another column

I would add 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   Report Post  
Old 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
Default 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:
I would add 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.



  #7   Report Post  
Old 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
Default Count entries in one column based on values in another column

I agree with your answer but based my reply on the need to "focus my efforts
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:
I would add 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.




  #8   Report Post  
Old 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
Default 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:

I agree with your answer but based my reply on the need to "focus my efforts
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:
I would add 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.






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Return Matched Numeric Values across Rows Sam via OfficeKB.com Excel Worksheet Functions 2 January 2nd 07 11:03 PM
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 03:56 AM
count values in one column based on their realtionship with anoth. shopaholic Excel Worksheet Functions 1 December 9th 05 01:13 AM
Count cells based on date range in another column [email protected] New Users to Excel 1 May 5th 05 08:11 PM
Formula to compare multiple rows values based on another column? Murph Excel Worksheet Functions 4 February 21st 05 02:44 AM


All times are GMT +1. The time now is 09:30 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017