Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    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>

  2. #2
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting Rows (Excel 2002)

    =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.

  4. #4
    4 Star Lounger
    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:
    1. <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
    COUNTA (A:A) works for number 1 but is there a way to use a formula to get the answer for 2 & 3?

    TIA
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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>

  6. #6
    4 Star Lounger
    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>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •