Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Pleasant Hill, CA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count if not <space> (Excel XP)

    I'd like to count the number of cells that have a value greater than 0. My COUNTIF function is not quite right. I'm sure this is a simple correction for the Loungers...please see cell H10. The formula would be more like =COUNTIF(B10:F10,>0). I'm expecting a value of 2 since 2003 and 2005 have values greater than zero. Thank you.

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count if not <space> (Excel XP)

    You need to add "s(double quotes) around your >0

    =COUNTIF(B10:F10,">0")

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Pleasant Hill, CA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count if not <space> (Excel XP)

    Thanks, mbarron. That's all it needed! The subject of this post is a little misleading since I had originally been checking for a <space> in the result cell, but I wasn't sure how to write that in the function. I switched to a result of zero to make it quantifiable. Was also curious to know if I could check noncontiguous cells so that my formula was =COUNTIF(B10,D10,F10,">0") but it didn't like that :-)

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

    Re: Count if not <space> (Excel XP)

    The COUNTIF function only works with a contiguous range. Even if you give a non-contiguous range a name (using Insert | Name | Define...), you cannot use the name in COUNTIF.
    COUNTIF will ignore blank cells and text values, so perhaps you could use =COUNTIF(B10:F10,">0"). And, of course, you can combine two or more COUNTIFs:

    =COUNTIF(B10:F10,">0")+COUNTIF(B15:F15,">0")

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count if not <space> (Excel XP)

    I know you have solved your problem now but if, in future, you want to count spaces in a string (say, in A1 stating "Sarah Likes Excel") you an use this formula:

    =IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1) the result will be 2

    or if you want to check if there is just a space in a cell:

    =COUNTIF(B10:F10," ") .......(where there is a space in between the inverted commas.

    Hope this helps <img src=/S/duck.gif border=0 alt=duck width=23 height=23>
    Jerry

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Count if not <space> (Excel XP)

    Very late to the party again, but you can use COUNTIF with non-contiguous cells (though it's ugly) like so:
    <code>=SUM(COUNTIF(INDIRECT({"A1","A3","A8"}),">0" ))</code>
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Count if not <space> (Excel XP)

    Thanks. That might come in handy sometimes, but it quickly becomes unwieldy if many cells are involved.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Count if not <space> (Excel XP)

    Yep - it doesn't work with named ranges either unfortunately so you do have to list each one!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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