MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Another simple quetion for you expert


Posted by Mack on July 16, 2001 8:21 AM

but not for me

=COUNTIF(Sheet1!$D$14:$D$48,"C")


If no data in that range , I want it to display nothing and not 0. Any ideas ?

Thanks in advance


Posted by Aladin Akyurek on July 16, 2001 8:43 AM

=IF(ISNUMBER(SEARCH((Sheet1!$D$14:$D$48,"C")),COUNTIF(Sheet1!$D$14:$D$48,"C"),"")

or

=IF(COUNTIF(Sheet1!$D$14:$D$48,"C")>0,COUNTIF(Sheet1!$D$14:$D$48,"C"),"")

Note that both formula incur a performance cost (they both compute the same thing twice). You can avoid this by formatting the cell of this formula as

[=0]"";General

This is a valuable tip by Mark W.

Aladin