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.

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.
>

On Oct 4, 4:56 pm, "Bob Phillips" <bob....@somewhere.com> 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" <daveg...@comcast.net> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> >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 -

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Oct 4, 4:56 pm, "Bob Phillips" <bob....@somewhere.com> 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" <daveg...@comcast.net> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>>
>>
>> >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 -
>
>

On Oct 5, 4:11 am, "Bob Phillips" <bob....@somewhere.com> 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" <daveg...@comcast.net> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > On Oct 4, 4:56 pm, "Bob Phillips" <bob....@somewhere.com> 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" <daveg...@comcast.net> wrote in message
>
> >>news:(E-Mail Removed)...
>
> >> >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.