Formula for summing unique values and also corresponding uniquevalues based on a parameter

Hi everyone,

Possibly a very confusing title, so I'll put my problem in more plain English, hoping someone can help.

I have a sheet which contains 3 main columns - Ticket Number, Agent Name, and Met SLA (yes=1, no =0). What I am trying to arrive at is a percentage for each agent on the total of unique tickets they have worked on that have hit SLA. The ticket number and the Met SLA values will always be the same across duplicate rows. One agent could work on the same ticket more than once so I only want to know about the ticket and its hit SLA or not once. And more than one agent could work on the same ticket (still the SLA hit never changes).

Example spreadsheet attached. In this sheet I need to calculate total number of unique tickets by Agent (in this case 3 for Chris, 4 for Tim) and the sum of unique tickets that have a corresponding 1 in column D (SLA met - this never changes by ticket) by Agents (in this case 2 for Chris, 3 for Tim) and then divide the latter by the former to get the % across 2 cells - one for Chris and one for Tim.

So in this sheet I want to populate G5 and G6.

Not sure how well I explained this so feel free to ask questions.

Thanks!

Hugh