Count a frequency of a digit from a list

[gfaulds]

Hello all,

I'm trying to count the frequency of the values 0-9 show up in a list of data. Details below. First column is my data, second columns are the numbers and the third is the amount of times that number appears in the list. For example the number 1 value should be 13, since its shows up multiple times for values such as 1, 11, 21, 15, etc. I'm sure its simple but Im not seeing it. Thank you.

I appreciate all the help.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Marcelo Branco]

Try

Pasta1
	A	B	C
1	Data	Numbers	Result
2	1	1	13
3	2	2	6
4	3	3	2
5	4	4	2
6	5	5	2
7	6	6	2
8	7	7	2
9	8	8	2
10	9	9	2
11	10	0	2
12	11
13	12
14	13
15	14
16	15
17	16
18	17
19	18
20	19
21	20
22	21
23	22
Plan1

Cell Formulas
Range	Formula
C2:C11	C2	=SUM(IF(IFERROR(--MID(A$2:A$23,{1,2},1),"")=B2,1))
Press CTRL+SHIFT+ENTER to enter array formulas.

M.

 

[Marcelo Branco]

A simpler formula

Pasta1
	A	B	C
1	Data	Numbers	Result
2	1	1	13
3	2	2	6
4	3	3	2
5	4	4	2
6	5	5	2
7	6	6	2
8	7	7	2
9	8	8	2
10	9	9	2
11	10	0	2
12	11
13	12
14	13
15	14
16	15
17	16
18	17
19	18
20	19
21	20
22	21
23	22
Plan1

Cell Formulas
Range	Formula
C2:C11	C2	=SUMPRODUCT(--(MID(A$2:A$23,{1,2},1)=B2&""))

M.

 

[Rick Rothstein]

This will also work (placed in C2:C11, normally entered) no matter how large the numbers in your cells are...

=SUMPRODUCT(LEN(A$2:A$23)-LEN(SUBSTITUTE(A$2:A$23,B2,"")))

 

[Marcelo Branco]

Rick,

I think there is a typo in your formula.
Shouldn't the second parameter in SUBSTITUTE be B2 instead of A2?
(BTW, by chance your formula showed the correct result in C11 )

M.

 

[Rick Rothstein]

Rick,

I think there is a typo in your formula.
Shouldn't the second parameter in SUBSTITUTE be B2 instead of A2?

Yes! Thanks for noticing that. I corrected my original post to reflect this.

 

[gfaulds]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Apologies. Fixed.

 

[gfaulds]

A simpler formula

Pasta1
	A	B	C
1	Data	Numbers	Result
2	1	1	13
3	2	2	6
4	3	3	2
5	4	4	2
6	5	5	2
7	6	6	2
8	7	7	2
9	8	8	2
10	9	9	2
11	10	0	2
12	11
13	12
14	13
15	14
16	15
17	16
18	17
19	18
20	19
21	20
22	21
23	22
Plan1

Cell Formulas
Range	Formula
C2:C11	C2	=SUMPRODUCT(--(MID(A$2:A$23,{1,2},1)=B2&""))

M.

Works perfectly. Thank you everyone for the help.

 

[Painterguy]

Hello all,

I'm trying to count the frequency of the values 0-9 show up in a list of data. Details below. First column is my data, second columns are the numbers and the third is the amount of times that number appears in the list. For example the number 1 value should be 13, since its shows up multiple times for values such as 1, 11, 21, 15, etc. I'm sure its simple but Im not seeing it. Thank you.

I appreciate all the help.


View attachment 45627

Incidentally, although it's a small sample, it looks to me like your values show signs of developing a Benford curve, in which case when you plug in the rest you won't need to calculate the frequencies, ypu can predict them as follows:
1) 30.1%
2) 17.6%
3) 12.5%
4) 9.7%
5) 7.9%
6) 6.7%
7) 5.8%
8) 5.1%
9) 4.6%

Of course, Benford is about leading digits and so doesn't account for zero and I don't know what kind of data you're working with. The high proportion of 1s and 2s could be a coincidence or a bias, no way to know from a small sample, but it might be worth having a look at Benford's Law and see if it might save you some time.