Count if not (Excel XP)

Topic

New Reply

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.

Reply | Quote

Replies

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

=COUNTIF(B10:F10,”>0″)

Reply | Quote

Thanks, mbarron. That’s all it needed! The subject of this post is a little misleading since I had originally been checking for a 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

Reply | Quote

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″)

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Yep – it doesn’t work with named ranges either unfortunately so you do have to list each one!

Reply | Quote

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

Reply | Quote