countif a1:a100 not numeric

[jsb1921]

A1:a100. will have either a 2 digit number or "--" (hyphen once or twice) or "__" (underscore once or twice)

=countif(a1:a12,">0" works fine.
Looking for a syntax for
1. =countif(a1:a12 "is not numeric") or something like that.
2. instead of using countif(a1:a12,">0") which works, if I can use some thing like countif(a1:a12,"isnumber"), i would be like it better.
3. on row 17, i found the average, by dividing the sumif and countif I have in row 15, and 16. and it does the job. Is there a way to use the excel average function, excluding the non-numeric cells.
thanks
john

countif.a.xlsx
	A	B	C
1	37
2	_	1 underscore
3	-	1 hyphen
4	44
5	56
6	--	2hyphens
7	28
8	30
9	__	2underscores
10	47
11	88
12	62
13
14
15	8	=COUNTIF(A1:A12,">0")	countif numeric
16	392	=SUMIF(A1:A12,">0")	sumif numeric
17	49	=A16/A15	avgif numeric
18	0	=COUNTIF(A1:A12,"not number")	countif not numeric
Sheet1

Cell Formulas
Range	Formula
A15	A15	=COUNTIF(A1:A12,">0")
A16	A16	=SUMIF(A1:A12,">0")
A17	A17	=A16/A15
A18	A18	=COUNTIF(A1:A12,"not number")

 

[Eric W]

=COUNT(A1:A12)

will count the numeric values, so

=ROWS(A1:A12)-COUNT(A1:A12)

will give the non-numeric.

 

[jsb1921]

Thanks. it works. I marked it as solution. My curiosity is: still, I would like to learn how to use isnumber excel function inside the countif conditon. appreciate, if you can help

 

[Eric W]

You can't use ISNUMBER directly inside COUNTIF. The arguments for COUNTIF are range, pattern (a text value). You can't use FUNCTION(range), and using ISNUMBER on the pattern won't help. There are a few options though. See this:

Book1
	A	B	C
1	37
2	_	1 underscore
3	-	1 hyphen
4	44
5	56
6	--	2hyphens
7	28
8	30
9	__	2underscores
10	47
11	88
12	62
13
14
15	8	8	countif numeric
16	392	392	sumif numeric
17	49		avgif numeric
18	4	4	countif not numeric
Sheet5

Cell Formulas
Range	Formula
B15	B15	=COUNTIFS(A1:A12,"<>-*",A1:A12,"<>_*")
B16	B16	=SUM(A1:A12)
A15	A15	=SUMPRODUCT(--ISNUMBER(A1:A12))
A16	A16	=SUMPRODUCT(--ISNUMBER(A1:A12),A1:A12)
A17	A17	=A16/A15
A18	A18	=SUMPRODUCT(--NOT(ISNUMBER(A1:A12)))
B18	B18	=SUM(COUNTIF(A1:A12,{"-*","_*"}))

 

[jsb1921]

Eric, This is EXACTLY what I was looking for, a way to make use of isnumber function in some way or other.