Formula to calculate how many times a digit occurs in a given range

[Ironman]

Hi

I would be very grateful for a formula that will result in the number of times a single digit - e.g. 6 - occurs in the range 001 to 500.

Many thanks!

 

[Eric W]

Look at the ones column of the first 10 numbers. How many times does 6 appear? Note that the pattern of the first 10 numbers repeats for the rest of the 490 numbers. How many times does it repeat? Now repeat for the tens column. Find a repeating pattern, count how many times 6 appears in that pattern, find out how many times the pattern repeats. Repeat the process for the hundreds column (should be very easy!).

 

[Ironman]

Thanks Eric, but is there a formula that will do this without having to manually count the occurrence?

 

[Eric W]

This sounds a lot like homework, which many people here (myself included) don't feel is appropriate to answer directly. Hints are OK. Yes, there is a formula, but it's easily derivable from the steps I explained. You shouldn't have to "count" anything more than 10. This formula, incidentally, will NOT be the same for say, the digit 3 or 5. Without understanding the reasoning behind the formula, you won't be able to adapt it to the different digits.

 

[Ironman]

I'm sorry, that's incorrect. I'm long retired. I have a set of sticky labels from 1 to 500. There are no leading zeros.

The manufacturer has told me he cannot make numbers in excess of 500. I need to produce numbers from 501 to 999 myself by cutting out the hundred digits from 5 to 9 using the existing numbers and I need to know how many more packets I will need to buy to create the 501-999 with the digits that are available from 1-500.

 

[Eric W]

I'm sorry if I misinterpreted the situation, but have you even tried my suggestion yet?

In the ones column, the pattern 1,2,3,4,5,6,7,8,9,0 repeats 50 times, and the 6 occurs once, so 50*1. The tens columns goes from 0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,2,2,2, etc. and repeats 5 times. 6 occurs 10 times in that range (60-69), so 5*10. Total is 50+50 or 100. There are no 6s in the hundreds column. But as you can see, from 501 to 999 there will be 6s, in fact 200 of them. Low numbers like 3 are reversed 200 in the 001-500 range, 100 in the 501-999 range. So you'll need to buy 2 sets of 001-500 to make one set of 501-999.

Yes, I could make a formula to do this, but a little analysis is quicker. Final recommendation: find another manufacturer. Seems like a lot of cutting.

 

[markmzz]

Hi!

Try the formula below:

=SUMPRODUCT(LEN($A$2:$A$501)-LEN(SUBSTITUTE($A$2:$A$501,D2,"")))

Where D2 have the digit you want to count.

Markmzz

 

[Ironman]

Apologies Eric, I was too tired last night to think your solution through.

Many thanks Mark, your formula was really helpful and is spot on, after checking it against the 'Find All' function.

 

[markmzz]

Many thanks Mark, your formula was really helpful and is spot on, after checking it against the 'Find All' function.

You are welcome and thanks for the return.

Markmzz