Excel counting unique value in mutiple values cells

[Apple08]

Hi All

I like to count an unique value e.g. 'blue' from cells with multiple values such as red, blue.. And I need to separate the counting based on two criteria eg. A and X as shown below. Please could anyone give me an idea how to setup this formula. Many thanks.

A	X	red, blue
A	Y	blue, green, yellow
A	X	blue
B	Y	red, blue
B	X	blue, green, yellow
B	Y	blue
C	X	red, blue
C	Y	green
C	X	blue, green, yellow
Counting Result:
A	X	blue	2​
A	Y	blue	1​
B	X	blue	1​
B	Y	blue	2​
C	X	blue	2​
C	Y	blue	0​

 

[jasonb75]

Here is one method that works. As your profile doesn't shown an excel version, I've assumed that you have the latest one. If that is not the case then please update your profile.

Book1
	A	B	C	D
1	A	X	red, blue
2	A	Y	blue, green, yellow
3	A	X	blue
4	B	Y	red, blue
5	B	X	blue, green, yellow
6	B	Y	blue
7	C	X	red, blue
8	C	Y	green
9	C	X	blue, green, yellow
10
11	Counting Result:
12	A	X	blue	2
13	A	Y	blue	1
14	B	X	blue	1
15	B	Y	blue	2
16	C	X	blue	2
17	C	Y	blue	0
Sheet6

Cell Formulas
Range	Formula
D12:D17	D12	=COUNT(FILTER(ROW(C1:C9),(A1:A9=A12)*(B1:B9=B12)*ISNUMBER(SEARCH(" "&C12&","," "&C1:C9&",")),""))

 

[Apple08]

Thank you