Can any of the cells contain both "this" and "that"?Hello,
I want to count the cells in a column that do not say "this" or "that" and ignore blank cells.
Thanks in advance for your help
my range is a1:a25, so change as needed:
Code:=counta(a1:a25)-countif(a1:a25,"this")-countif(a1:a25,"that")
=COUNTA(A:A)-COUNTIF(A:A,"*this*")-COUNTIF(A:A,"*that*")+COUNTIF(A:A,"*this*that*")
That double counts if any cells contain both "this" and "that" and doesn't account for strings that include either word. See post #6.perfect!!! Thank you!!!
I think you need to add back in the case for "*that*this*" as well.Code:=COUNTA(A:A)-COUNTIF(A:A,"*this*")-COUNTIF(A:A,"*that*")+COUNTIF(A:A,"*this*that*")
Good catch Rick! That would make it:I think you need to add back in the case for "*that*this*" as well.
=COUNTA(A:A)-COUNTIF(A:A,"*this*")-COUNTIF(A:A,"*that*")+COUNTIF(A:A,"*this*that*")+COUNTIF(A:A,"*that*this*")