I have a spreadsheet in which I log letters stating the results of
inquiries from various agencies. The inquiry status is either Found
("F") or Not Found ("N") in the G column on the spreadsheet. Every
week, I log more letters, each new one added to the top of the list
under the column headers. A macro is used to insert a properly
formatted new line at the top of the list for each new entry, which is
in Row 4. The entry is numbered in column A, which gets the number by
simply adding 1 to the number in the row below, row 5.

Now I want to know what percentage of the letters are reporting found
and I want it automatically updated as I add new entries. I don't know
how to enter the formula to do the COUNTIF function on a changing
range. Each time I add a new entry, I want the COUNTIF range to
include the new row. The current number of rows is available right
there in cell A4, so what is the formula to do the COUNTIF from cell
G4 to the cell A4's value below? Is this clear? In other words, I want
to do a COUNTIF from cell G4 to the cell the number of rows below
specified by the value in cell A4.

Thanks in advance!

Give this a go.
=COUNTIF(INDIRECT("G4:G" & A4),"N")
davegb wrote:
I have a spreadsheet in which I log letters stating the results of
inquiries from various agencies. The inquiry status is either Found
("F") or Not Found ("N") in the G column on the spreadsheet. Every
week, I log more letters, each new one added to the top of the list
under the column headers. A macro is used to insert a properly
formatted new line at the top of the list for each new entry, which is
in Row 4. The entry is numbered in column A, which gets the number by
simply adding 1 to the number in the row below, row 5.

Now I want to know what percentage of the letters are reporting found
and I want it automatically updated as I add new entries. I don't know
how to enter the formula to do the COUNTIF function on a changing
range. Each time I add a new entry, I want the COUNTIF range to
include the new row. The current number of rows is available right
there in cell A4, so what is the formula to do the COUNTIF from cell
G4 to the cell A4's value below? Is this clear? In other words, I want
to do a COUNTIF from cell G4 to the cell the number of rows below
specified by the value in cell A4.

Thanks in advance!

On Sep 27, 12:49 pm, JW wrote:
Give this a go.
=COUNTIF(INDIRECT("G4:G" & A4),"N")


Thanks! Worked great, and gave me some understanding of how to use the
INDIRECT function!



davegb wrote:
I have a spreadsheet in which I log letters stating the results of
inquiries from various agencies. The inquiry status is either Found
("F") or Not Found ("N") in the G column on the spreadsheet. Every
week, I log more letters, each new one added to the top of the list
under the column headers. A macro is used to insert a properly
formatted new line at the top of the list for each new entry, which is
in Row 4. The entry is numbered in column A, which gets the number by
simply adding 1 to the number in the row below, row 5.

Now I want to know what percentage of the letters are reporting found
and I want it automatically updated as I add new entries. I don't know
how to enter the formula to do the COUNTIF function on a changing
range. Each time I add a new entry, I want the COUNTIF range to
include the new row. The current number of rows is available right
there in cell A4, so what is the formula to do the COUNTIF from cell
G4 to the cell A4's value below? Is this clear? In other words, I want
to do a COUNTIF from cell G4 to the cell the number of rows below
specified by the value in cell A4.

Thanks in advance!- Hide quoted text -

- Show quoted text -