Most frequent text with criteria

[AnonCow]

Hello all, I am having some trouble with a fairly complex Excel formula.

I am looking to find two things, the most frequent occurrence of a text within an array given criteria and also the number of that occurrence.

More specifically, under the 'Analysis' sheet of the provided Excel doc, there are two blank rows. The first is "most assists" (column S) and the second is "Number of assists" (column U). In the sheet "Play by Play", under column F there is a name of a player who scored a dunk, and column D is the player who assisted the player under column F (if applicable).

I am looking to, under the analysis sheet, find the player who provided the most assists the player under column D, as well as the number of assists to that player. A specific example would be in the 'Analysis' Sheet cell D6, the player is "Nic Claxton". Under column S, I am looking to find what player assisted Nic Claxton the most (i.e. "Kevin Durant"), and in column U how many assists did that player provide to Nic (essentially the number of occurrences of "Kevin DUrant" in the assist column with "Nic Claxton" in the player column)

My understanding would be something along the lines of =INDEX(rng1,MODE(IF(rng2=criteria,MATCH(rng1,rng1,0)))), where rng1 is the column D in the "Play by Play" sheet, rng2 is column in the "Analysis sheet", but I have not been able to get this working as of yet. I consistently get the NA error.

Is anyone able to help?

 

[rondeondo]

Hi,
tricky, from the description you would need to have a count of assists generated per player then do a lookup for the player and the max number of assists. would be handy if you can put up a sample file using the XL2BB function, also put in a example of the desired output. Another complexity is if two players provide the same number of assists, how do you want to show that result? You can use a countif to see how many players provide x assists.