count if and ignore blanks

[lachone]

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

 

[JoeMo]

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

Can any of the cells contain both "this" and "that"?

 

[lachone]

yes

 

[NickRed18]

My range is A1:A25, so change as needed:

=COUNTA(A1:A25)-COUNTIF(A1:A25,"this")-COUNTIF(A1:A25,"that")

 

[lachone]

my range is a1:a25, so change as needed:

=counta(a1:a25)-countif(a1:a25,"this")-countif(a1:a25,"that")

perfect!!! Thank you!!!

 

[JoeMo]

yes

=COUNTA(A:A)-COUNTIF(A:A,"*this*")-COUNTIF(A:A,"*that*")+COUNTIF(A:A,"*this*that*")

 

[JoeMo]

perfect!!! Thank you!!!

That double counts if any cells contain both "this" and "that" and doesn't account for strings that include either word. See post #6.

 

[Rick Rothstein]

=COUNTA(A:A)-COUNTIF(A:A,"*this*")-COUNTIF(A:A,"*that*")+COUNTIF(A:A,"*this*that*")

I think you need to add back in the case for "*that*this*" as well.

 

[NickRed18]

I had no idea about asterisks behaving that way! Is that a property unique to COUNT, or does it work for Match, Lookup, etc.

 

[JoeMo]

I think you need to add back in the case for "*that*this*" as well.

Good catch Rick! That would make it:

=COUNTA(A:A)-COUNTIF(A:A,"*this*")-COUNTIF(A:A,"*that*")+COUNTIF(A:A,"*this*that*")+COUNTIF(A:A,"*that*this*")