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(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") |