Google Sheets Formula that finds employee with most hours worked for a specific location and shift (INDEX/MODE with multiple Criteria)

Question

In Google Sheets, I need to find out which employee works more than 50% of the hours for a given shift at a given location based on timecard data (Table in col G:J). In the attached google sheet I need a formula in Column C that returns the name of the matching employee useing Column A & B for criteria, then column D and E should auto calcuate.

https://docs.google.com/spreadsheets/d/1HE90e1hVvsEIiFbNdb3sQ042LHMZhxyCno72eZMA79k/edit?usp=sharing

I was previously using an ArrayFormula with INDEX/IF/MODE/MATCH (example in cell C15) but that could only return the employee with the most shifts and was not able to differentiate by criteria.

Thanks in Advance!

user_5632629 · Answer

delete everything in C2:D7 and use this in C2:

=INDEX(IFNA(VLOOKUP(A2:A6&B2:B6, SORT(QUERY({H2:H&J2:J, G2:G, I2:I}, 
 "select Col1,Col2,count(Col1),sum(Col3) group by Col1,Col2"), 3, 0), {2, 4}, 0)))

Google Sheets Formula that finds employee with most hours worked for a specific location and shift (INDEX/MODE with multiple Criteria)

1 Answers