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

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:

=SUMPRODUCT(--ISNUMBER(FIND(" "&B$1&" ",SUBSTITUTE(REPT(" ",10)&$A$2:$A$15&REPT(" ",10),",",REPT(" ",10)))))

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

You are welcome. Thank you for the feedback.

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)