Counting digits in a range up to a max value

[gerald8000]

How can I count the number of didgits in a range up to a max value?

If Cell A1 had a value of 23. How can I calculate the B3:B12?

[TABLE="width: 500"]
<tbody>[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]23
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Number of 1's (1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 21)
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Number of 2's (2, 12, 22) =
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Number of 3's (3, 13, 23) =
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Number of 4's (4, 14) =
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Number of 5's (5, 15) =
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Number of 6's (6, 16) =
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]Number of 7's (7, 17) =
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Number of 8's (8, 18) =
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]Number of 9's (9, 19) =
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]Number of 0's (10, 20) =
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]

 

[gerald8000]

Hi XOR LX, yes I seem to have messed up the 2's ..... should be:

Number of 2's (2, 12, 20, 21, 22) = 6

 

[Veritan]

I'm guessing that's because you don't have the text "Number of 1's", "Number of 2's", etc. in cells A3:A12 on the sheet. The code needs those in there in order to know where to put the results. You might be able to get away with having just the numbers in those cells, but I'd stick with the "Number of 1's" format.

Edit: I tested it out, you can have any text in cells A3:A12 that you like, so long as somewhere in there it has each number from 1 to 0, and those numbers are unique to a cell.

 

[Eric W]

Here is a formula based solution:

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]{=SUM(LEN(ROW(INDIRECT("1:"&$A$1)))-LEN(SUBSTITUTE(ROW(INDIRECT("1:"&$A$1)),A3,"")))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Put the number you want in A1. Put the list of digits in A3:A12. Then put the B3 formula in, confirm with Control+Shift+Enter. Then copy it down the column. I suspect there's a more efficient version, but I haven't figured that out yet.

 

[gerald8000]

Hi Eric W,

You have saved my day. That works a treat. Thank you very much.

Gerald

 

[Rick Rothstein]

Hi Eric W,

You have saved my day. That works a treat. Thank you very much.

Does it really? What about if you typed 43 in cell A1... what would the number 3 be for you, 15 (Eric's formula) or 6 (where, as you examples show, 10 is added to each number starting with 3 and ending with the value in cell A1)?

 

[XOR LX]

Does it really? What about if you typed 43 in cell A1... what would the number 3 be for you, 15 (Eric's formula) or 6 (where, as you examples show, 10 is added to each number starting with 3 and ending with the value in cell A1)?

Aren't you forgetting about the numbers from 30-32 and 34-39?

Regards

 

[Rick Rothstein]

Aren't you forgetting about the numbers from 30-32 and 34-39?

In his original post, the OP showed values incremented by 10's so for the number 3 ending at 43, I would think he wanted the 3's counted from this sequence...

3, 13, 23, 33, 43

in which there are only six 3's, not from this one...

3, 4, 5, 6, etc.

 

[Eric W]

It could be that I misunderstood the question, especially since the OP had a few typos. However, I tried this as a check. If you use 43 as an example, there are 9 1-digit numbers, and 34 2-digit numbers, meaning there are 34 * 2 + 9 = 77 total digits. If you put 43 in A1 and sum up B3:B12, you get 77. I've been able to find numbers matching the totals for all digits, and XOR LX found the additional 3's.

I believe the OP wanted to count all the 3's whether they are in the 1's or 10's position. Look at the example of the 2's. The typos in the original post made it indefinite, but he did clarify the numbers with 2's to include in a later post.

 

[Ingolf]

Try:-

[COLOR=navy]Sub[/COLOR] MG13Oct06
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] nStr [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] Dic [COLOR=navy]As[/COLOR] Object
[COLOR=navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
[COLOR=navy]For[/COLOR] n = 1 To [a1]
    nStr = nStr & n
[COLOR=navy]Next[/COLOR] n
[COLOR=navy]For[/COLOR] n = 1 To Len(nStr)
    [COLOR=navy]If[/COLOR] Not Dic.exists(Mid(nStr, n, 1)) [COLOR=navy]Then[/COLOR]
       Dic.Add (Mid(nStr, n, 1)), 1
    [COLOR=navy]Else[/COLOR]
        Dic(Mid(nStr, n, 1)) = Dic(Mid(nStr, n, 1)) + 1
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] n
Range("A3").Resize(Dic.Count, 2) = Application.Transpose(Array(Dic.Keys, Dic.items))
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Mike, in row with 2, 12, 22 your code result is 7 and correct is 4. Can you correct that.

 

[Rick Rothstein]

It could be that I misunderstood the question, especially since the OP had a few typos. However, I tried this as a check. If you use 43 as an example, there are 9 1-digit numbers, and 34 2-digit numbers...

I stand corrected... Messages #7 and #11 seems to back your interpretation of what the OP wanted.