CODEWITHSUNDEEP
excel
Wouter
Wouter

Reputation: 173

SUM of IndexMatch - Excel

I have an Excel file with 2 tabs. In the 1st tab Match Results I have all the tennis matches results form 2015 and 2016.

Example of Match Results Tab:

Match Results Tab

In the 2nd tab Players I'm running my analytics based on information in the Match Results tab.

Players Tab

In the Match Results tab most players have multiple entries as each player plays normally more than 1 game during the season.

In my Players tab, where I run my analytics, each player appears only once.

What I want to achieve is to find out the avarage number of games that a player is playing in the first Set of the total amount of tennis matches that he played. Regardless if the player is winning or losing the match.

The amount of games played in the 1st set are in column T of the Match Results tab.

The Winner of the match is in column Kof the Match Results tab, the loser of the match in column L of the Match Results tab

I've been playing around with IndexMatch, but I'm only getting the first total of number of games that are played in the match.

For example; When I search for "Duckworth J. (Column K2, Match Results tab), I'm only getting as result the number 8, which are the number of games played in the 1 set of the 1st match of the season that "Duckworth J." played.

In this case I know that "Duckworth J." played 17 matches in 2015/2016. So I first want to find the total amount of games that "Duckworht J." played during the 2015 and 2016 season. Than I want the average.

Upvotes: 1

Views: 95

Answers (1)

Andreas
Andreas

Reputation: 23958

=SUMPRODUCT((K4:L50="Donald")*T4:T50)/COUNTIF(K4:L50;"Donald")

^^ Will sum all "Donald" scores and devide by number of rows (that Donald is mentioned).

Edit: correct now? See now that I's K and L column I should have been looking at too.

Edit 2: to save your "Best of" column we need to use sumifs(). Below is the code to get "best of 3", note the last part of sumifs() J4:J50;3 where 3 is best of 3.

=SUMIFS(T4:T50;K4:L50;"Donald";J4:J50;3)/COUNTIF(K4:L50;"Donald")

Upvotes: 1

Related Questions