Counting Rows (Excel 2002)

Topic

New Reply

I need to determine the number of rows being used in column A. I think I need to use INDEX or CountIF or a combo of both but can’t seem to figure out how to do it. I’m sure it’s simple but I haven’t been able to get anything to work.

In another formula I need to figure out how many rows [in column A] are used starting from a certain row [i.e. 228 to the end]. I’m thinking it’s a derivative of the 1st formula. Any ideas?

TIA

Reply | Quote

Replies

=counta(A:A) counts all cells that are not empty.
=count(A:A) counts all cells that contain a number.

Reply | Quote

=COUNTA(A:A) will return the number of non-blank cells in column A. A cell containing a formula counts as non-blank, even if the formula results in a blank.

=COUNTA(A228:A65536) or =COUNT(A:A)-COUNTA(A1:A227) will return the number of non-blank cells in column A starting at A228.

Reply | Quote

Hans, that works except now I realize that I was attempting is to do a little more. Column A is a list of Chapter numbers, some of which begin with S. They are sorted so the Ss all fall in proper sequence but the numbers from 1 to Sxxx have missing numbers and at any time they could add a row in the regular chapter numbers [before the Ss] which would cause the COUNTA that references a specific row to be wrong. This is what I need to do:

1. How Many Rows Used in Total
2. How Many Rows Used From A1 To The First Chapter Beginning With An S
3. How Many Rows Used From The First Chapter Beginning With An S – To The End

COUNTA (A:A) works for number 1 but is there a way to use a formula to get the answer for 2 & 3?

TIA

Reply | Quote

For 3:

=COUNTIF(A:A,">=S")

For 2:

=COUNTA(A:A)-COUNTIF(A:A,">=S")

Reply | Quote

How simple! Thanks Hans!!

Reply | Quote