I have a data set of players and their corresponding jersey numbers. My actual data set is larger but this sample should suffice for all intents and purposes. This data set includes the various specialties that the player has. There are categories that each specialty (sport) falls into. Albeit the formatting, I was able to get some valuable help earlier this week which helped me in arriving at a total tally for a numeric category in a cell with other numbers and commas.

The function that helped me was as follows:

=SUM(--(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,SUBSTITUTE($D$3:$D$16,",","</s><s>"))&"</s></t>","//s[.=" & K3 & "]")<>""))

However, in the data I have now, there are situations where one player will play two games which both fall into the same category (i.e., player 10 plays Soccer and Baseball and they both are sports that require a ball). What I want, is to be able to find the # of players that play a single sport that falls into one of the many categories. For example, I would like to find the number of players that play a single sport that requires a ball. So if a player plays two sports that require a ball, the tally should only count this player once. Is there a function that can allow me to avoid double counting of players across a designated category? I have also attached a screenshot of my excel worksheet below.

CodePudding user response：

Solved (and edited)

I'm using Array formulas and "COUNTA" for solve both the [total tally] and the [player count] problems.

Formula for total tally: ->>> =COUNTA(FILTER($E$3:$E$30,NOT(ISERROR(FIND(","&H3&",",","&$E$3:$E$30&",",1)))))

Formula for player count: ->>> =COUNTA(UNIQUE(FILTER($C$3:$C$30,NOT(ISERROR(FIND(","&H3&",",","&$E$3:$E$30&",",1))))))