Project Count per Team Member Formula Needed

[akvpv6]

Good Morning,

I am looking to generate a formula that will show the name and number of projects a team member is working on. From the screenshot provided I want to know how many projects from column A are associated with a name from Column D (Project Team). An issue I am coming across is that under Project Team there can be multiple names per box, is there a work around for this in a formula?

Example would be showing that initial BS is associated with project Dashboard Example 3, and Dashboard Example 8

Thank you!

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F	G	H
1	Project Name	Project Description	Project by Category	Project Team			Team Members	Projects
2	Test		Ken Norton, Mickey Mouse				Baby Snookums	Test 3, Test 8
3	Test 2		Ben Vereen				Ben Vereen	Test 2, Test 6
4	Test 3		Baby Snookums				Edna Mode	Test 4
5	Test 4		Edna Mode				Ken Norton	Test, Test 5, Test 7
6	Test 5		Ken Norton				Mickey Mouse	Test
7	Test 6		Ben Vereen
8	Test 7		Ken Norton
9	Test 8		Baby Snookums
10
Sheet8

Cell Formulas
Range	Formula
G2:G6	G2	=SORT(UNIQUE(TRANSPOSE(TRIM(TEXTSPLIT(TEXTJOIN(",",1,C2:C9),",")))))
H2:H6	H2	=TEXTJOIN(", ",1,FILTER($A$2:$A$9,ISNUMBER(SEARCH(G2,$C$2:$C$9))))
Dynamic array formulas.

The H formula can probably be turned into a SPILL formula, but my version of Excel doesn't have BYROW.