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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,731
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,731
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,731
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*")
 

Watch MrExcel Video

Forum statistics

Threads
1,095,320
Messages
5,443,770
Members
405,251
Latest member
shanezer

This Week's Hot Topics

Top