Count Cells That Don't Contain the Following Text Strings

S

SteveC

=COUNTIF(Fundamentals!$B$2:$B$1000,OR("<>*honey*","<>*cows*"))

this doesn't work...

any suggestions?

thanks very much.
 
D

David F Cox

If I read this right it will count all records that have neither Honey or
cows in the text

I think it is equivalent to NOT AND("=*honey*","=*cows*"))

since you have not told us what you want I have no suggestions.
 
L

L. Howard Kittle

If I understand correctly, this may do what you want.

=COUNTA(D1:D10)-(COUNTIF(D1:D10,"honey")+COUNTIF(D1:D10,"cows"))

Change the D1:D10 to yours.

HTH
Regards,
Howard
 
R

Robert McCurdy

Probably this would be safer, as its not clear if 'honey' or 'cows' could appear within any cell together.

=SUMPRODUCT(N(LEN(SUBSTITUTE(SUBSTITUTE(Fundamentals!$B$2:$B$1000,"cows",""),"honey",""))=LEN(Fundamentals!$B$2:$B$1000)))


Regards
Robert McCurdy

=COUNTIF(Fundamentals!$B$2:$B$1000,OR("<>*honey*","<>*cows*"))

this doesn't work...

any suggestions?

thanks very much.
 
Top