Formula count number names in column excluding 0

[bishop7262]

I have a column with list of names but there are some zeros in that column. What is the formula to only count the names and exclude the zeros

example

Dave Myer
John Doe
Stacy Keach
0
0
0

Count should equal 3

thank you

 

[Aladin Akyurek]

=IFERROR(INDEX(List_Tier,MATCH(0,COUNTIF($B$2:B6,List_Tier),0)),"")

This formula yields a blank, an item of 0 length. The formula Hotpepper forwards will only count text values with a lenght 1 or more, so it will do what you want.

 

[Coryj5]

Jasonb75 "><" is much easier than mine, but i have also seen this used in similar situations. simply counting the common cell that you do not want and subtracting it from the total count. this only works when the data you do not want is always the same.

=COUNTA(A1:A7)-COUNTIF(A1:A7,"0")

 

[Aladin Akyurek]

Jasonb75's "><" is much easier than mine, but i have also seen this used in similar situations. simply counting the common cell that you do not want and subtracting it from the total count. this only works when the data you do not want is always the same.

=COUNTA(A1:A7)-COUNTIF(A1:A7,"0")

Give a try to:

COUNTIF(A1:A7,"?*")

too.

 

[T. Valko]

Try

=COUNTIF(A2:A7,"><")

Using >< instead of <> ignores all numeric values.

I've never seen that criteria before.

Check out these results:

Book1
	A	B	C	D	E
1	text1	_	_	16	=COUNTIF(A1:A20,"?*")
2	text2	_	_	3	=COUNTIF(A1:A20,"><")
3	text3	_	_	_
4	0	_	_	_
5	0	_	_	_
6		formula blank	_	_
7		formula blank	_	_
8	_		_	_
9	???	_	_	_
10	*	_	_	_
11	/
12	"
13	)
14	(
15	&
16	^
17	%
18	$
19	#
20	@
Sheet1

Seems as though it excludes all those "special" characters!

Maybe we can use that in data validation where one of the rules is no special characters are permitted?