Return value if unique value in cell has specific content in other columns for several rows

[Tupelo1984]

Dear,

I am looking for a solution for counting and returning a specific value if a unique value from a column has tagged specific information to it in other columns.

Count column should show the result as in mini sheet below.

Rule:

Count if 'Code' (my unique value) has either "TestApp" and/or "Production" in App column with returning 1, and results of App (if one of these two apps are found), and 0 if none of these apps are found

sample.xlsx
	A	B	C
1	code	App	Count
2	100	TestApp	1, Test App and Production
3	100	TestApp	1, Test App and Production
4	100	Production	1, Test App and Production
5	200	Production	1, Production
6	200	Production	1, Production
7	200	Production	1, Production
8	300	TestApp	1, Test App
9	300	Development	1, Test App
10	300	Development	1, Test App
11	300	Development	1, Test App
12	400	Development	0
13	400	Development	0
sample

Thank you!

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C
1	code	App	Count
2	100	TestApp	1, TestApp and Production
3	100	TestApp	1, TestApp and Production
4	100	Production	1, TestApp and Production
5	200	Production	1, Production
6	200	Production	1, Production
7	200	Production	1, Production
8	300	TestApp	1, TestApp
9	300	Development	1, TestApp
10	300	Development	1, TestApp
11	300	Development	1, TestApp
12	400	Development	0
13	400	Development	0
Master

Cell Formulas
Range	Formula
C2:C13	C2	=LET(Txt,TEXTJOIN(" and ",,UNIQUE(FILTER($B$2:$B$20,($A$2:$A$20=A2)*(($B$2:$B$20="TestApp")+($B$2:$B$20="Production")),""))),IF(LEN(Txt),"1, "&Txt,"0"))

 

[Tupelo1984]

Hi Fluff, Thanks for your reply! I get the NAME? error - any idea what this can be caused by? Thank you!

sample.xlsx
	A	B	C
1	code	App	Count
2	100	TestApp	#NAME?
3	100	TestApp	1, Test App and Production
4	100	Production	1, Test App and Production
5	200	Production	1, Production
6	200	Production	1, Production
7	200	Production	1, Production
8	300	TestApp	1, Test App
9	300	Development	1, Test App
10	300	Development	1, Test App
11	300	Development	1, Test App
12	400	Development	0
13	400	Development	0
sample

Cell Formulas
Range	Formula
C2	C2	=LET(Txt,TEXTJOIN(" and ",,UNIQUE(FILTER($B$2:$B$20,($A$2:$A$20=A2)*(($B$2:$B$20="TestApp")+($B$2:$B$20="Production")),""))),IF(LEN(Txt),"1, "&Txt,"0"))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Ok, you may not have the LET function, so try

=IF(LEN(TEXTJOIN(" and ",,UNIQUE(FILTER($B$2:$B$20,($A$2:$A$20=A2)*(($B$2:$B$20="TestApp")+($B$2:$B$20="Production")),"")))),"1, "&TEXTJOIN(" and ",,UNIQUE(FILTER($B$2:$B$20,($A$2:$A$20=A2)*(($B$2:$B$20="TestApp")+($B$2:$B$20="Production")),""))),"0")