COUNTIF syntax to NOT count a cell

[skinfreak]

I am using the following to look for cells that contain the text "beef". How would I modify it to count all cells that do not have "beef" in them?

=COUNTIF(Q5:Q9000,"*"&"beef"&"*")

Thanks

John

 

[bjwillingham]

Try this:

=Counta(Q5:Q9000) - COUNTIF(Q5:Q9000,"*"&"beef"&"*")

 

[Scott Huish]

bjwillinham's solution would give you the count of which ones do not contain "beef" of the existing entries, or do you want to know how many do not contain "beef" in the total range?

In other words, if Q5:Q9000 were completely empty would you want it to return 0 or 8996?

 

[skinfreak]

I would want it to return 0. I am not interested in blank cells.

 

[fairwinds]

Hi,

Try:

=SUMPRODUCT((Q5:Q9000<>"")-ISNUMBER(SEARCH("beef",Q5:Q9000)))