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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,692
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,692
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,692
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,089,240
Messages
5,407,095
Members
403,125
Latest member
vbambre

This Week's Hot Topics

Top