MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Countif / If Function

Posted by Rob on March 06, 2001 10:50 AM

How would I be able to use the countif function to search a range of cells for a certain value and display it in another cell, say A1, at the same time if the result was "0" occurances I want to leave the cell blank rather then having a zero. I cannot seem to figure it out. I attempted:

I can get the blank cell for the zero result, but cannot get the right formula to display the result if say apples appeared 5 times. I do not know what to type for "x" so that the cell will also display 5, for instance.

Thanks for the help.

Posted by Rob on March 06, 2001 10:58 AM

Thanks anyway, I figured out a formula that works. I am using:


Posted by Mark W. on March 06, 2001 11:35 AM

Rob, there's a lot of computational overhead with
your solution:


If it just a matter of not seeing a zero why don't
you format the A1 as 0;;; instead, and just use

Alternately, you could use
=TEXT(COUNTIF(E9:H9,"apples"),"#") and right-align
the resultant text.