Home 
Search 
Today's Posts 
#1




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




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




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




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




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




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




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




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 
Display Modes  


Similar Threads  
Thread  Forum  
Return Matched Numeric Values across Rows  Excel Worksheet Functions  
Need to Improve Code Copying/Pasting Between Workbooks  Excel Discussion (Misc queries)  
count values in one column based on their realtionship with anoth.  Excel Worksheet Functions  
Count cells based on date range in another column  New Users to Excel  
Formula to compare multiple rows values based on another column?  Excel Worksheet Functions 