|
COUNTIF on changing range
I have a spreadsheet that logs letters I send out to various agencies.
Each time I add a letter to the log, I use a macro which creates a new line at the top of the log in row 3 so newest letters are at the top. In column A is the index number of the letter which is simply 1 added to the previous index no. In column G is a status indicator, either an "F" or an "N". I want to count the Fs in column G and divide by the total number of letters. The total is there in A3. The following formula works fine, until I add a new row: =COUNTIF(INDIRECT("G3:G" & A3+2),"F")/A3 When I add a new row, the "G3", and both "A3"s increment to G4 and A4 respectively, and the formula omits row 3 from the count. As I add more lines, the new lines are not included in the count. I'd like to know how to get around this problem. I've tried several combinations to make the 3 a constant, one of which is: =COUNTIF(INDIRECT("G"&"3:G"&"3"+2),"f")/"a"&"3" but it returns a #REF or sometimes a #VALUE error. Does anyone know how to write this formula so it works? Thanks for the help. |
COUNTIF on changing range
Why not just use
=COUNTIF(G:G,"F")/COUNTA(G:G) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "davegb" wrote in message ups.com... I have a spreadsheet that logs letters I send out to various agencies. Each time I add a letter to the log, I use a macro which creates a new line at the top of the log in row 3 so newest letters are at the top. In column A is the index number of the letter which is simply 1 added to the previous index no. In column G is a status indicator, either an "F" or an "N". I want to count the Fs in column G and divide by the total number of letters. The total is there in A3. The following formula works fine, until I add a new row: =COUNTIF(INDIRECT("G3:G" & A3+2),"F")/A3 When I add a new row, the "G3", and both "A3"s increment to G4 and A4 respectively, and the formula omits row 3 from the count. As I add more lines, the new lines are not included in the count. I'd like to know how to get around this problem. I've tried several combinations to make the 3 a constant, one of which is: =COUNTIF(INDIRECT("G"&"3:G"&"3"+2),"f")/"a"&"3" but it returns a #REF or sometimes a #VALUE error. Does anyone know how to write this formula so it works? Thanks for the help. |
COUNTIF on changing range
On Oct 4, 4:56 pm, "Bob Phillips" wrote:
Why not just use =COUNTIF(G:G,"F")/COUNTA(G:G) It's close, but I'm not getting the correct answer. I think the column headers in rows 1 & 2 maybe throwing the count off. I tried to add correction factors for those, but I'm still not getting the correct number for the percentage. Any other ideas? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "davegb" wrote in message ups.com... I have a spreadsheet that logs letters I send out to various agencies. Each time I add a letter to the log, I use a macro which creates a new line at the top of the log in row 3 so newest letters are at the top. In column A is the index number of the letter which is simply 1 added to the previous index no. In column G is a status indicator, either an "F" or an "N". I want to count the Fs in column G and divide by the total number of letters. The total is there in A3. The following formula works fine, until I add a new row: =COUNTIF(INDIRECT("G3:G" & A3+2),"F")/A3 When I add a new row, the "G3", and both "A3"s increment to G4 and A4 respectively, and the formula omits row 3 from the count. As I add more lines, the new lines are not included in the count. I'd like to know how to get around this problem. I've tried several combinations to make the 3 a constant, one of which is: =COUNTIF(INDIRECT("G"&"3:G"&"3"+2),"f")/"a"&"3" but it returns a #REF or sometimes a #VALUE error. Does anyone know how to write this formula so it works? Thanks for the help.- Hide quoted text - - Show quoted text - |
COUNTIF on changing range
Perhaps
=COUNTIF(G:G,"F")/(COUNTA(G:G)-1) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "davegb" wrote in message oups.com... On Oct 4, 4:56 pm, "Bob Phillips" wrote: Why not just use =COUNTIF(G:G,"F")/COUNTA(G:G) It's close, but I'm not getting the correct answer. I think the column headers in rows 1 & 2 maybe throwing the count off. I tried to add correction factors for those, but I'm still not getting the correct number for the percentage. Any other ideas? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "davegb" wrote in message ups.com... I have a spreadsheet that logs letters I send out to various agencies. Each time I add a letter to the log, I use a macro which creates a new line at the top of the log in row 3 so newest letters are at the top. In column A is the index number of the letter which is simply 1 added to the previous index no. In column G is a status indicator, either an "F" or an "N". I want to count the Fs in column G and divide by the total number of letters. The total is there in A3. The following formula works fine, until I add a new row: =COUNTIF(INDIRECT("G3:G" & A3+2),"F")/A3 When I add a new row, the "G3", and both "A3"s increment to G4 and A4 respectively, and the formula omits row 3 from the count. As I add more lines, the new lines are not included in the count. I'd like to know how to get around this problem. I've tried several combinations to make the 3 a constant, one of which is: =COUNTIF(INDIRECT("G"&"3:G"&"3"+2),"f")/"a"&"3" but it returns a #REF or sometimes a #VALUE error. Does anyone know how to write this formula so it works? Thanks for the help.- Hide quoted text - - Show quoted text - |
COUNTIF on changing range
On Oct 5, 4:11 am, "Bob Phillips" wrote:
Perhaps =COUNTIF(G:G,"F")/(COUNTA(G:G)-1) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "davegb" wrote in message oups.com... On Oct 4, 4:56 pm, "Bob Phillips" wrote: Why not just use =COUNTIF(G:G,"F")/COUNTA(G:G) It's close, but I'm not getting the correct answer. I think the column headers in rows 1 & 2 maybe throwing the count off. I tried to add correction factors for those, but I'm still not getting the correct number for the percentage. Any other ideas? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "davegb" wrote in message roups.com... I have a spreadsheet that logs letters I send out to various agencies. Each time I add a letter to the log, I use a macro which creates a new line at the top of the log in row 3 so newest letters are at the top. In column A is the index number of the letter which is simply 1 added to the previous index no. In column G is a status indicator, either an "F" or an "N". I want to count the Fs in column G and divide by the total number of letters. The total is there in A3. The following formula works fine, until I add a new row: =COUNTIF(INDIRECT("G3:G" & A3+2),"F")/A3 When I add a new row, the "G3", and both "A3"s increment to G4 and A4 respectively, and the formula omits row 3 from the count. As I add more lines, the new lines are not included in the count. I'd like to know how to get around this problem. I've tried several combinations to make the 3 a constant, one of which is: =COUNTIF(INDIRECT("G"&"3:G"&"3"+2),"f")/"a"&"3" but it returns a #REF or sometimes a #VALUE error. Does anyone know how to write this formula so it works? Thanks for the help.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - Thanks, Bob. It turns out that I have to subtract 3, which I can't figure out why. There are 2 rows of column headers, so I thought I should subtract 2, but that didn't work. Very confusing. But it works. |
| All times are GMT +1. The time now is 11:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com