help with countif function

[kateb]

I've written a spreadsheet that tracks data throughout the year. I hav
one worksheet for each month, plus a worksheet where the data i
summarised.
I wish to count the number of times the word 'rapid' appears on eac
months' worksheet so have used the countif formula to do this. I hav
pasted this onto the summary worksheet changing the reference to eac
months' sheet as appropriate. This works fine for sheets containin
data (i.e. currently Jan-June) but for sheets that currently have n
data (i.e. July -Dec) I get 0. This is not entirely true as it is no
really 0 because there is no data there yet!
I tried to use the iserror command, but there is no error in th
formula so I still get zeros. So I tried using isblank, which ha
worked on other formulae within the same spreadsheet, but this gave m
zeros too!
The formula I'm using is
COUNTIF(May!B:B,"*rapid*")
I'm thinking that a variation on
IF(ISBLANK(COUNTIF(May!B:B,"*rapid*")),"",(COUNTIF(May!B:B,"*rapid*
might just do the trick, but I cannot get it to work.
I would like the cell to remain blank if the equation cannot b
calculated due to no data in the worksheet it is looking at.

Kat

 

[DDM]

Kate, try this:

=IF(COUNTIF(May!B:B,"*rapid*")=0,"",COUNTIF(May!B:B,"*rapid*"))

 

[Peo Sjoblom]

=IF(COUNTA(May!B:B),COUNTIF(May!B:B,"*rapid*"),"")

however is there a reason to use 65536 rows? If not select an appropriate
number like
May!B2:B1000 or something since COUNTA will count each cell and it might
slow down your
workbook

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[kateb]

Many thanks. I knew there had to be a simple solution!!

And thanks for doing it so speedily too!

Kate