Sum of individual digits (just for fun)

[Hans Knudsen]

I want to sum how often a certain digit (for example 1) appear in
the in the sequence of whole numbers 1, 2, ,...., N

N 1 10 11 21 ....
f(N) 1 2 4 13 ....

N=21 ---> f(N)= number of ones in {1, 10, 11, 12, 13, 14 ,15 ,16,
17 ,18, 19, 21}=13

I have mmade an attempt with the following array formula:
=CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A11))),1))
which for N=1 returns 49, for N=10 returns {49,48} and so on.
How can I extend above formula (count the "49's") so that I get the
results shown under
f(N) or maybe a better way?

Hans Knudsen

 

[Ron de Bruin]

Hi Hans

Try this for every 1 in A1:A10

=SUMPRODUCT(--(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"1",""))))

 

[Hans Knudsen]

Hi Ron
Of course. How simple can it be!
Thank you very much.

Regards
Hans