Count the numbers within a string of numbers in a single cell

**gra0001** · 06-25-2016, 07:52 PM

Hi,
Well I have tried and tried. Hope you can help.
I have a column of numbers in "G" Say it starts at G2 and goes to G100 (See Below)
The numbers have a comma between but not leading or trailing, they are all in 1 cell.
I know if I take them out of a single sell I can do but the Qty of numbers changes and I would like to try to solve as is.
2,7,9,11,13,15
3,8,9,12,13,15
4,7,10,11,14,15
5,8,10,12,14,15
6,9,10,13,14,15
1,2,3,16,17,18
1,4,5,16,17,19
2,4,6,16,18,19
3,5,6,17,18,19
1,7,8,16,17,20
2,7,9,16,18,20
3,8,9,17,18,20
4,7,10,16,19,20
5,8,10,17,19,20
I want to have a reference cell that counts the times a number appears in that column G
EG Number 1 3
Number 2 4
So I need a formula that can do this.
Much appreciated
David

**FlameRetired** · 06-25-2016, 08:17 PM

This is one way.

With the target numbers in row one and filled to the right to highest number (I just took it to the 7s) enter this formula in B2 and fill across.

Formula:

Please Login or Register  to view this content.

Row\Col	A	B	C	D	E	F	G	H
1		1	2	3	4	5	6	7
2	2,7,9,11,13,15	3	4	4	4	4	3	5
3	3,8,9,12,13,15
4	4,7,10,11,14,15
5	5,8,10,12,14,15
6	6,9,10,13,14,15
7	1,2,3,16,17,18
8	1,4,5,16,17,19
9	2,4,6,16,18,19
10	3,5,6,17,18,19
11	1,7,8,16,17,20
12	2,7,9,16,18,20
13	3,8,9,17,18,20
14	4,7,10,16,19,20
15	5,8,10,17,19,20

**Phuocam** · 06-25-2016, 08:24 PM

or try this ...

=(SUMPRODUCT(LEN(","&$G$2:$G$100&","))-SUMPRODUCT(LEN(SUBSTITUTE(","&$G$2:$G$100&",",","&A1&",",","))))/LEN(A1&",")

**gra0001** · 06-25-2016, 08:44 PM

Thanks,
I now know why I couldn't do it.
And to now understand where I was wrong helps me into the future.
I must admit I was stressing out to the max trying to make it happen
Thank you USA and Vietnam

David

**FlameRetired** · 06-25-2016, 08:50 PM

You are welcome. Thank you for the feedback.

**Tony Valko** · 06-26-2016, 05:08 AM

It looks like no numbers will be repeated in a cell.

Here's another one...

Data Range

	A	B	C	D
1		------	Number	Count
2	2,7,9,11,13,15		1	3
3	3,8,9,12,13,15		2	4
4	4,7,10,11,14,15		3	4
5	5,8,10,12,14,15		4	4
6	6,9,10,13,14,15		5	4
7	1,2,3,16,17,18		6	3
8	1,4,5,16,17,19		7	5
9	2,4,6,16,18,19		8	5
10	3,5,6,17,18,19		9	5
11	1,7,8,16,17,20		10	5
12	2,7,9,16,18,20		11	2
13	3,8,9,17,18,20		12	2
14	4,7,10,16,19,20		13	3
15	5,8,10,17,19,20		14	3

This formula entered in D2 and copied down:

=SUMPRODUCT(LEN(","&A$2:A$15&",")-LEN(SUBSTITUTE(","&A$2:A$15&",",","&C2&",","")))/(LEN(C2)+2)

Count the numbers within a string of numbers in a single cell

LinkBack

Thread Tools

Rate This Thread

Display

Count the numbers within a string of numbers in a single cell

Re: Count the numbers within a string of numbers in a single cell

Re: Count the numbers within a string of numbers in a single cell

Re: Count the numbers within a string of numbers in a single cell

Re: Count the numbers within a string of numbers in a single cell

Re: Count the numbers within a string of numbers in a single cell

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Count numbers between two values in a coma delimited string of numbers

[SOLVED] Count occurances of numbers in a particular range in a single cell

[SOLVED] how to count occurence of numbers separated by , in a single cell

[SOLVED] how to count occurence of numbers separated by , in a single cell

how to count occurence of numbers separated by , in a single cell

how to count occurence of numbers separated by , in a single cell

how to count occurence of numbers separated by , in a single cell

how to count occurence of numbers separated by , in a single cell

Bookmarks

Bookmarks

Posting Permissions