How to look up specific text in a specific column, multiple columns involved

I will assume you are using a simple =TRANSPOSE() on the rows in Table 2 to create the spill in the summary table (i.e. so we can avoid extra lookup/sorting).  Then you just need a formula to do the counting in the lists.  Here are 2 options:

Using LAMBDA helper BYCOL:

=LET(data,F4:.AAA100, TRANSPOSE(BYCOL(data,LAMBDA(c, SUM(--ISNUMBER(SEARCH($B$2,c)))))))

Using MMULT to do a matrix sum:

=LET(data,F4:.AAA100, TRANSPOSE(MMULT(SEQUENCE(1,ROWS(data),1,0),--(ISNUMBER(SEARCH($B$2,data))))))

since table 2 isn't a formal table I use the shortcut :. to do a TRIMRANGE and assumed an arbitrary max column of AAA and max row of 100.  If the data exceeds that you will need to adjust and if there is other data on the sheet in that region you will need to adjust (one of the advantages of Defining as a Table instead of just using a range)

I will attached the updated sheet with both examples