I tried that to get the count of the number 1. It seems to count all the ones.

The formula result was 8. It should be 1.

What am I doing wrong? Here's the formula I used-
=SUMPRODUCT((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,"1",""))))/1



"Teethless mama" wrote:

=SUMPRODUCT((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,"17",""))))/2


"Mac" wrote:

Hello,
having a column in which data is entered like (1 line here denoted 1 cell)

1,14,17
2
3,8
12,14
14,14,17

the usual countif won't work, of course. How do I go about a formula such as
- "give me a count of all occurrences of the number 17 in the given range"?