Count Cells Containing specific number, when cell contains multiple numbers

[Dom10]

Is it possible to count the number of instances of a specific number in a cell, if the cell has multiple, comma separated numbers in it.

Eg: 1,19,20

I tried using =COUNTIF(A$2:A$4,"*"&C2&"*")+COUNTIF(A$2:A$4,C2)

but is appears to count 11 as 1, if there is other content in the cell. It also could not distinguish between 112,1 and 11,1.... it would count both of these as 11.

Any ideas, greatly appreciated.

 

[Eric W]

Welcome to the forum!

Try:

Book2
	A	B	C	D
1	List		Number	Count
2	11,1,5		1	1
3	11,4		2	1
4	4,11		3	1
5	11		4	3
6	3,4		5	1
7	2,11		11	5
Sheet3

Cell Formulas
Range	Formula
D2:D7	D2	=SUMPRODUCT(--ISNUMBER(FIND(","&C2&",",","&$A$2:$A$7&",")))

 

[Fluff]

I suggest that you update your Account details (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’)

How about

+Fluff v2.xlsm
	A	B	C	D
1
2	11,1,5		1	1
3	11,4		2	1
4	4,11		3	1
5	11		4	3
6	3,4		5	1
7	2,11		11	5
8
Master

Cell Formulas
Range	Formula
D2:D7	D2	=SUMPRODUCT(--(ISNUMBER(FIND(","&C2&",",","&$A$2:$A$7&","))))

 

[Dom10]

I suggest that you update your Account details (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’)

How about

+Fluff v2.xlsm
	A	B	C	D
1
2	11,1,5		1	1
3	11,4		2	1
4	4,11		3	1
5	11		4	3
6	3,4		5	1
7	2,11		11	5
8
Master

Cell Formulas
Range	Formula
D2:D7	D2	=SUMPRODUCT(--(ISNUMBER(FIND(","&C2&",",","&$A$2:$A$7&","))))

Thanks.
Details updated as follows:
Microsoft Excel for Office 365 MSO (16.0.12527.21378) 32-bit

 

[Dom10]

Welcome to the forum!

Try:

Book2
	A	B	C	D
1	List		Number	Count
2	11,1,5		1	1
3	11,4		2	1
4	4,11		3	1
5	11		4	3
6	3,4		5	1
7	2,11		11	5
Sheet3

Cell Formulas
Range	Formula
D2:D7	D2	=SUMPRODUCT(--ISNUMBER(FIND(","&C2&",",","&$A$2:$A$7&",")))

Thanks! That does the trick.

However, seeing as I can't really decipher what that formula is doing, I wonder if you can help me my second element. Perhaps I should have asked the question all in one go, but I thought I would be able to use COUNTIFS for this second part (however, I cannot see how that would work using the SUMPRODUCT formula you suggested).

Once I have identified which Items are related to which Serial Number, I then want to see how many are Completed. The Status column either has proposed date or Completed.

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G
1	Item	List	Status		Serial Number	Count	% Completed
2	A	12,1,5	Completed		1	5	40%
3	B	1	Completed		2	3	33%
4	C	2,1	12/1/2021		3	1	0%
5	D	1,2	2/15/2021		4	1	0%
6	E	3,4,1	3/15/2021		5	1	100%
7	F	11,2	Completed		11	1	100%
Sheet1

Cell Formulas
Range	Formula
F2:F7	F2	=SUMPRODUCT(--ISNUMBER(FIND(","&E2&",",","&$B$2:$B$7&",")))
G2:G7	G2	=SUMPRODUCT(--ISNUMBER(FIND(","&E2&",",","&$B$2:$B$7&",")),--($C$2:$C$7="Completed"))/F2

 

[Dom10]

Try:

Book1
	A	B	C	D	E	F	G
1	Item	List	Status		Serial Number	Count	% Completed
2	A	12,1,5	Completed		1	5	40%
3	B	1	Completed		2	3	33%
4	C	2,1	12/1/2021		3	1	0%
5	D	1,2	2/15/2021		4	1	0%
6	E	3,4,1	3/15/2021		5	1	100%
7	F	11,2	Completed		11	1	100%
Sheet1

Cell Formulas
Range	Formula
F2:F7	F2	=SUMPRODUCT(--ISNUMBER(FIND(","&E2&",",","&$B$2:$B$7&",")))
G2:G7	G2	=SUMPRODUCT(--ISNUMBER(FIND(","&E2&",",","&$B$2:$B$7&",")),--($C$2:$C$7="Completed"))/F2

Oh my! That is excellent. Thank you so much. That is so elegant. Thanks so much for sharing your knowledge.

 

[Eric W]

Glad we could help!

 

[Peter_SSs]

Excel for Office 365

In that case, some alternatives:

21 01 05.xlsm
	A	B	C	D	E	F	G
1	Item	List	Status		Serial Number	Count	% Completed
2	A	12,1,5	Completed		1	5	40.0%
3	B	1	Completed		2	3	33.3%
4	C	2,1	1/12/2021		3	1	0.0%
5	D	1,2	15/02/2021		4	1	0.0%
6	E	3,4,1	15/03/2021		5	1	100.0%
7	F	11,2	Completed		11	1	100.0%
Completed

Cell Formulas
Range	Formula
F2:F7	F2	=COUNT(FIND(","&E2&",",","&B$2:B$7&","))
G2:G7	G2	=COUNT(FILTER(F$2:F$7,(C$2:C$7="Completed")*ISNUMBER(FIND(","&E2&",",","&B$2:B$7&",")),""))/F2