count based the number of digits

[stratis]

Hallo,

I' m trying to find a sollution to the following problem.
I have a collumn with numbers (in the followin example collumn "Code"), which their lenght vary (from 2 to 10 digits). In another collumn (Collumn "Result from the formula") I would like to have a formula that will count the times that the number in each row appears to the collumn "Code" (the formula should count based on the digits that each numbers has). For example in collumn "Code" you may find the data and in collumn "Result from the formula" you may find what I want to succeed:

[TABLE="width: 199"]
<tbody>[TR]
[TD="align: center"]Code[/TD]
[TD="align: center"]Result from the formula[/TD]
[/TR]
[TR]
[TD="align: right"]746[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]747[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]748[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]7482[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]741[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]7500[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]7502[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]7507[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]7508[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]7509[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]75[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]8500000000[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2040090002[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Are there any suggestions?

 

[Rickmaurinus]

Hi,

You can try the following formula in cell B2:

=SUMPRODUCT( --(ISNUMBER(FIND(A2;$A$2:$A$14))))

Then paste it to to the below cells.

Cheers,
Rick

 

[Aladin Akyurek]

Care to explain how 2 obtains for 748, for example?

 

[Peter_SSs]

You can also use the array formula shown. It should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B
1	Code	Result
2	746	1
3	747	1
4	748	2
5	7482	1
6	741	1
7	7500	1
8	7502	1
9	7507	1
10	7508	1
11	7509	1
12	75	6
13	8500000000	1
14	2040090002	1
Count

 

[stratis]

You can also use the array formula shown. It should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Count

	A	B

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]Code[/TD]
[TD="align: right"]Result[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]746[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]747[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]748[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]7482[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]741[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]7500[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]7502[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]7507[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]7508[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: right"]7509[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="align: right"]8500000000[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD="align: right"]2040090002[/TD]
[TD="align: right"]1[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 {=COUNT(FIND(A2,A$2:A$14))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you very much!!!
It really works perfect!!!

 

[Peter_SSs]

Thank you very much!!!
It really works perfect!!!

You are very welcome.
Just pointing out though that mine was just an alternative - Rickmaurinus' suggestion should also work for you.