# Thread: Count if not <space> (Excel XP)

1. ## 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. ## Re: Count if not <space> (Excel XP)

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

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

3. ## 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. ## 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. ## 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>

6. ## 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>

7. ## 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. ## 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!

#### Posting Permissions

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