Index / Match / Large / Sumif???

[Buggirl3216]

Background:
I have a list of names of people who have volunteered with my organization and the daily hours. I would like to write an equation that looks at the list of names, summarizes the hours for the people, and returns the top 5 volunteers in a certain timeframe (like the month of January). I was able to write this equation: =INDEX($B$4:$B$14, MATCH(1, ($C$4:$C$14=LARGE($C$4:$C$14, E4)) * (COUNTIF(F$2:F2, $B$4:$B$14)=0), 0)) and it pulls the top 5 volunteers for a single day and takes into consideration that some individuals may tie for the number of hours. You can see that working below. You can see that "Edgar" is placed 5th with 2 hours even though he should be placed 2nd with 7 hours. Any thoughts?

I know that I can create another table that pulls the names and then write a "sumif" equation to get the totals and then use the TOTALS table to index. I was hoping that I could just write it all into one equation.

Trisha

Names	Total Hrs		Place	Name	Hours
Dom	3​		1​	Adam	9​
Ben	5​		2​	Ben	5​
Edgar	1​		3​	Dom	3​
Charlie	3​		4​	Charlie	3​
Adam	9​		5​	Edgar	2​
Edgar	2​
Edgar	2​
Edgar	2​

 

[arthurbr]

Why not simply use a Pivot Table and set the Filter to Top 5 ? Excel Pivot Table Filters - Top 10

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)