How to write a count-if-not function?

J

Jsatter

I am trying to write a function that counts the the cells within a range that
do not include a certain phrase.
 
J

Jon Quixley

Jsatter,

Take a look at the IS Functions in Excel, I am sure there is somethin
here that will help

Cheers
Jo
 
R

Rob Hick

the quickest way to achieve what you're after is to use the FIND
function to search the text, which will return an error if it can't
find the relevant text. You can then use the ISERROR function with a
'--' infront to return ones and zeros which you can simply add up. You
will need to enter this as an array formula (hold ctrl+shift whilst
pressing enter).

So an example might be:

=SUM(--ISERROR(FIND("foobar",A1:A5)))


Rob
 
Top