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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,746
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,746
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,746
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,821
Messages
5,446,708
Members
405,413
Latest member
AlainCar

This Week's Hot Topics

Top