How to create a formula in my excel if I want count list of number by 10?

[yeongkwe]

Hi, everyone, I'm new here and new in excel also, and I hope you guys, can help me. I had this problem, How to create a formula in my excel if I want count list of number by the jump 9?

(Example of list of number, 001 t 100) Like the blow example, 001 and 002 is two counts, which within (000 to 009) every jump by 9. second example, 010 and 014 is also two counts, which within (010 to 019) which within every jump by 9. last example, 31, is only one count, which within (030 to 039) which within every jump by 9. I got alot of the number until 99999. I now doing it manually one by one count like that I show you below. I believe will should be a smarter why which I can make you of the powerful excel to help me to count and provide me the count number result on the beside the end of the number of each jump of 9. I believe there are alot of expert can kindly help me regarding this issue. Your help will be very much appreciated. Thanks you.

001
002 (2)
010
014 (2)
020
026
027 (3)
031 (1)
049 (1)
061 (1)
074 (1)
95
96
97
98
99 (5)
100 (1)

 

[arthurbr]

Are your values numbers (formatted with preceding 0's) or text looking like numbers?
In the latter case, fist select the column then Data - Text to columns - Click "Finish " immediately


Supposing your range starts in A1, in B1 enter =IF(INT(A2/10)<>INT(A1/10),SUMPRODUCT(--(FLOOR(($A$1:A1)/10;1)=INT(A1/10))),"") and pull down as needed