Complicated COUNTIFS

I'm trying to put together a summary sheet for a table of data. The data is arranged as field = column, record = row, as you would expect.

The COUNTIFS I'm using so far are working well....stuff like:

Formula:

=IF(ISBLANK(E6), "", COUNTIFS(JobData!E:E,E6,JobData!AA:AA,"Commercial*"))

however.....this one is beyond me.



The data consists of a primary key (JOB_ID) in column A, a second unique identifier (SERVICE_ORDER_ID) in column B, and around 30 other fields. The field I'm looking at is the EngineerName field (Column E) that contains the name of the engineer that completed the job, and obtaining totals of various types of job. However, one of the fields I'm looking at is the RECALL field (column R), which....if the job was a second visit to the same customer to rectify an issue still apparent after the 1st visit, will contain the SERVICE_ORDER_ID of the first job, along with some other data......and if not then will contain NULL.

I need to be able to obtain a total number of jobs that have resulted in a recall for each engineer. Bearing in mind, the second job may have been completed by a different engineer to the 1st job.


so...in English....something like:


Count number of jobs with this engineers name listed in column "E", the SERVICE_ORDER_ID (column B) of which is listed in the first 15 characters of column "R" of all other jobs.


Any ideas?