Results 1 to 6 of 6
Thread: Counting Rows (Excel 2002)

20050121, 17:07 #1
 Join Date
 Jan 2001
 Location
 Michigan, USA
 Posts
 408
 Thanks
 0
 Thanked 0 Times in 0 Posts
Counting Rows (Excel 2002)
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<font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

20050121, 17:14 #2
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting Rows (Excel 2002)
=counta(A:A) counts all cells that are not empty.
=count(A:A) counts all cells that contain a number.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20050121, 17:18 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Counting Rows (Excel 2002)
=COUNTA(A:A) will return the number of nonblank cells in column A. A cell containing a formula counts as nonblank, even if the formula results in a blank.
=COUNTA(A228:A65536) or =COUNT(A:A)COUNTA(A1:A227) will return the number of nonblank cells in column A starting at A228.

20050121, 18:04 #4
 Join Date
 Jan 2001
 Location
 Michigan, USA
 Posts
 408
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting Rows (Excel 2002)
Hans, that works except now I realize that I was attempting is to do a little more. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15> 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:
 <LI>How Many Rows Used in Total
<LI>How Many Rows Used From A1 To The First Chapter Beginning With An S
<LI>How Many Rows Used From The First Chapter Beginning With An S  To The End
TIA<font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

20050121, 18:09 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Counting Rows (Excel 2002)
For 3:
<code>=COUNTIF(A:A,">=S")</code>
For 2:
<code>=COUNTA(A:A)COUNTIF(A:A,">=S")</code>

20050121, 18:51 #6
 Join Date
 Jan 2001
 Location
 Michigan, USA
 Posts
 408
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting Rows (Excel 2002)
<img src=/S/doh.gif border=0 alt=doh width=15 height=15> How simple! Thanks Hans!!
<font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>