Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting cells with any value in a range (2003)

    Hi all,
    I have a spreadsheet where I have to count the number of cells in a range, B3:H6, that has data in in them. The data will be text (numbers and letters), but I only need the total number of cells with data in them.
    I tried the IFCOUNT function but couldn't get the result I was hoping for.
    Any advice?
    Thanks.

    Bill

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting cells with any value in a range (2003)

    <code>
    =COUNTA(B3:H6)
    </code>

    will count the number of cells that are not empty. Is that what you want?
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting cells with any value in a range (2003

    Yes it is, but for some reason the cell is only displaying the formula. Is there a reason for that?

    Thanks heaps Leonard. Much appreciated.

    Bill

  4. #4
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting cells with any value in a range (2003

    Sorry Legare, to clarify, I only want it to count the number of cells in the range with data in it. Not the value of those cells.

    Thanks.

    Bill

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

    Re: Counting cells with any value in a range (2003

    Make sure that
    a) the cell containing the COUNTA formula hasn't been formatted as text in the Number tab of Format | Cells...
    [img]/forums/images/smilies/cool.gif[/img] your worksheet hasn't been set to display formulas in cells in the View tab of Tools | Options...

  6. #6
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting cells with any value in a range (2003

    Thanks Hans. I fixed the formatting, which fixed the problem
    I appreciate your help.

    Bill

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting cells with any value in a range (2003

    That should be what the COUNTA does. It counts the cells that are NOT blank (ie contain something). If that is not what you mean by "data", then we will need a better description of what you want.
    Legare Coleman

Posting Permissions

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