If / Countif array formula in table with numbers, numbers with text, and text

[willt201]

Trying to count any number > 0.1 including numbers with an asterix infront of them, ignoring any text only entry.
So, if B1 = TOP, B17 will show how many values are > 0.1
I need to do this in a single formula and not by braking it down and creating/hiding extra colums of data.
Something along the lines of {=IF(B1="TOP",COUNTIF(ISNUMBER(SUBSTITUTE (B2:B15,"*","")),">0.1"),"")}, which is clearly beyond my abilities.
Thanks

Item	TOP
1	2.6
2	0.0
3	*Ave
4	Avr
5	*0.1
6	*Ave
7	*
8	*0.2
9	*7.8
10	*6.4
11	0.1
12	58.0
13
14	154.2
Total	204.4
Count>0.1

<tbody>
</tbody>

 

[Eric W]

Welcome to the Board.

Try

=SUM(IFERROR(IF(SUBSTITUTE(B2:B15,"*","")+0>0.1,1),0))

and confirm with Control+Shift+Enter.

 

[Ron Coderre]

This regular formula seems to work...

B17: =IF(B1="TOP",SUMPRODUCT(--ISNUMBER(1/(--SUBSTITUTE(B2:B15,"*","")>0.1))),"")

Is that something you can work with?

 

[willt201]

Both work a treat, thanks Eric and Ron, appreciate your time and effort.

 

[Eric W]

Glad we could help.