How can I count the numbers greater than 18 in same cell in excel -

how can count numbers greater 18 in same cell in excel

20 26 37;28 17 not provided not provided not provided 17 30;26;6;4;3 not provided not provided 30

assuming that:

1) range in question a1:a5

2) given cell within range, if there multiple numbers within cell these numbers ever separated commas

then, array formula**:

=count(1/(1/(1/(0+(0&trim(mid(substitute(a1:a5,",",rept(" ",max(len(a1:a5)))),max(len(a1:a5))*(column(index(1:1,1):index(1:1,1+max(len(a1:a5)-len(substitute(a1:a5,",","")))))-1)+1,max(len(a1:a5)))))))<18))

change <18 @ end required.

regards

**array formulas not entered in same way 'standard' formulas. instead of pressing enter, first hold down ctrl , shift, , press enter. if you've done correctly, you'll notice excel puts curly brackets {} around formula (though not attempt manually insert these yourself).