I have a list of numbers in excel for example:

11111
12345
23222
13111
11511
92099

And I want to count the number of occurrence of the numbers So it will be like this

11111 5
12345 0
23222 4
13111 4
11511 4
92099 3

CodePudding user response：

=LET(ξ,MAX(LEN(A1)-LEN(SUBSTITUTE(A1,SEQUENCE(10,,0),""))),IF(ξ=1,0,ξ))

CodePudding user response：

Using SUMPRODUCT and MODE.SNGL:

=IFERROR(SUMPRODUCT(--(MODE.SNGL(--MID(A1,SEQUENCE(LEN(A1)),1))=--MID(A1,SEQUENCE(LEN(A1)),1))),0)

We can also use BYROW and LET to do the whole range at once:

=BYROW(
    A1:A6,
    LAMBDA(rg,
        LET(
            a,--MID(rg,SEQUENCE(LEN(rg)),1),
            IFERROR(SUMPRODUCT(--(MODE.SNGL(a)=a)),0))))

CodePudding user response：

This works on any character, not just numbers:

=LET(d,B8,
     s,MID(d,SEQUENCE(LEN(d)),1),
     m,XMATCH(s,s),
     f,MAX(FREQUENCY(m,m)),
IF(f=1,0,f))

First an array (s) is made of each single character in the cell.

m creates an array of the match result of s to itself.

f returns the max number of the frequency of the matches.

Because this could return 1 for all unique values, an if-statement is added to change to 0 in case of 1.