Required For Complex Lookup

I am trying to summarise training data in a spreadsheet as per the following layout:

A B C D E F 1 Trainer name # of trainees # of assessments Total hours Avg hrs per trainee Pass rate 2 Peter

3 Paul

4 Mary

The intention is to have this summary fixed on one tab, with the raw data being dumped into a second tab alongside the summary (named “the data”).

The data for the summary comes directly out of an Access database and is in the following format (I’ve put an X against the headings that aren’t relevant to the summary):

A B C D E F G H I J K L M N 1 X X PersID Name X X X X X Trainer Assessment X Result Hours 2

12345 Jimbo

Peter Excel
Competent 2 3

54321 Nelson

Paul Access
Not yet Competent 3
Notes:

-The data can be up to 2000 rows long

-There will be multiple entries for each trainer of course, and multiple entries for some trainees (i.e. they will complete several training types)

-Result can only be Competent or Not Yet Competent

So, of the 5 fields I need to populate for each trainer, I’ve currently only got the following nailed:

# of assessments: =COUNTIF('The data'!$A$2:$A$114,A2)
Avh hrs per trainee = D2/B2

These were the easy ones.

What I’m having trouble with is the following:

For the #of trainees field, the calculation must determine how many people were trained by each particular trainer – so only unique entries should be counted (PersID could be the unique identifier).
Total hours will also need to be linked to the individual trainer – in other words, how many hours in total has the trainer spent training?

Pass rate is another tricky one – for each trainer, I’d like to see the percentage of trainees deemed competent out of the total number of trainees they trained.