count if and ignore blanks

lachone

New Member
Joined
Jun 8, 2017
Messages
24
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,644
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"?
 

NickRed18

Board Regular
Joined
May 25, 2017
Messages
76
My range is A1:A25, so change as needed:
Code:
=COUNTA(A1:A25)-COUNTIF(A1:A25,"this")-COUNTIF(A1:A25,"that")
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,644
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.
 

NickRed18

Board Regular
Joined
May 25, 2017
Messages
76
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,644
I think you need to add back in the case for "*that*this*" as well.
Good catch Rick! That would make it:
Code:
=COUNTA(A:A)-COUNTIF(A:A,"*this*")-COUNTIF(A:A,"*that*")+COUNTIF(A:A,"*this*that*")+COUNTIF(A:A,"*that*this*")
 

Forum statistics

Threads
1,077,635
Messages
5,335,379
Members
399,014
Latest member
hamzalaarif

Some videos you may like

This Week's Hot Topics

Top