Count number of times a word exists in a column and displaying the count separated by a comma

Question

I have been struggling with this for weeks and haven't made much progress. I have two Google sheets. One has the data in it(Test Sheet 4) and the other is a summary sheet(Test Sheet 3). I would like for sheet 3 to count the number of different strings in a column, from sheet 4, matched to a couple criteria. I have a sample sheets below.

Test Sheet 3 Test Sheet 4

Test Sheet 4 contains the data. I would like The notes columns in Test Sheet 3 to show the count of each type. The formula needs to match the week number and color. There is an example on row 2. Please let me know if you have any questions. This might be worded confusingly.

Answer 1

paste in B3:

=ARRAYFORMULA(MMULT(IFERROR(SPLIT(REGEXREPLACE(INDIRECT(
 ADDRESS(ROW(C3), COLUMN(C3), 4)&":"&SUBSTITUTE(
 ADDRESS(ROW(C3), COLUMN(C3)), ROW(C3), )&
 MAX(IF($A3:$A="",,ROW($A3:$A)))), "[A-Za-z,]", ), " "))*1, 
 ROW(INDIRECT("A1:A"&COLUMNS(SPLIT(
 REGEXREPLACE(C3:C, "[A-Za-z,]", ), " "))))^0))

paste in C3:

=ARRAYFORMULA(IFNA(VLOOKUP(""&$A3:$A, 
 REGEXREPLACE(""&SPLIT(TRIM(TRANSPOSE(QUERY(QUERY(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(
 IMPORTRANGE("1pozEjZUfrQkDK3-P04rmaTUQqJkT4ZYAduCm8MwCB9g", B1&"!A2:A")&"♦♥"&
 IMPORTRANGE("1pozEjZUfrQkDK3-P04rmaTUQqJkT4ZYAduCm8MwCB9g", B1&"!F2:F"), 
 "select Col1,count(Col1) group by Col1 label count(Col1)''")),,999)), "♥")&",",
 "select max(Col2) group by Col2 pivot Col1"),,999))), "♦"), "^, |,$", ), 2, 0)))