Counting occurance of numbers in a cell

[LeeT]

Hi, I have an array formula that counts occurances of a certain number in a cell:

=SUMPRODUCT(LEN(","&Sheet1!$G8&",")-LEN(SUBSTITUTE(","&Sheet1!$G8&",",",1,",",,")))

The above looks in G8 and counts how many '1's occur. (if i'm looking for a double digit number i just divide by 2).

Problem is it works fine when numbers are entered like:1,4,1,5,7,10,etc. But if I put in:1,1,3,5,3,7, it only counts the number 1 once - I think its because the 1's are next to each other, because otherwise it works.

Anyone any idea how to make this work - if the same numbers are next to each other??

 

[GlennUK]

try this:

=(LEN(","&SUBSTITUTE(Sheet1!G8,",",",,")&",")-LEN(SUBSTITUTE(","&SUBSTITUTE(Sheet1!G8,",",",,")&",",",1,",",,")))

 

[LeeT]

Cheers - works an absolute treat!!

 

[Domski]

I use this UDF for the same purpose:

Public Function CountNums(rngMyRange As Range, lngVar As Long) As Long

Dim myArr
Dim lngItem As Long
Dim lngCount As Long

myArr = Application.Transpose(Split(rngMyRange, ","))

For lngItem = 1 To UBound(myArr, 1)

    If myArr(lngItem, 1) = lngVar Then CountNums = CountNums + 1

Next lngItem

End Function

Dom