Averageifs, filtering first occurrence of a number in another column

I am tasked with maintaining a spreadsheet that contains a tab dedicated to averaging job hours for orders of a certain type when specific criteria are met. To date, I have just used the AVERAGEIFS command to filter and average only certain values (Between two dates and order type).

The problem I am running into is the source data for the information skewing the overall averages (slightly) but enough to be a problem.

The source data is copied into several lines as "tasks" for each job and the total number of hours for each job is copied with it so when I take averages the hours are considered by a factor of how many tasks each job has.

For instance, job number 121 may have 3 tasks and the job has 12 hours. The 12 is copied 3 times in the AVERAGEIFS function. In another place job number 126 has 4 tasks and the job has 14 hours. The 14 is copied 4 times in the AVERAGEIFS function.

Because we have over 3000 lines of tasks and other tabs that use the repeated data (setup prior to my arrival), I cannot just simply remove the copied values. Its kinda needed to make other stuff work.

What I want to do is set a criteria where only the first occurrence of the job numbers will be indexed, the indexes matched to the other criteria in the AVERAGEIFS, and the number of resulting hours averaged.



So something like this =AVERAGEIFS( HRS_RANGE, TYPE_RANGE, TYPE, DATE_COMPLETED_RANGE, ">="&DESIRED_START_DATE, DATE_COMPLETED_RANGE, "<"&DESIRED_END_DATE, JOB_NUMBER, --CRITERIA FOR ONLY FIRST OCCURANCE OF EACH JOB NUMBER-- )

I can get the criteria to detect the 1st occurrence of the very first job and only consider that but I need it to detect ALL the first occurrences of EACH job number, like an indexed array or something.

Please see attached spreadsheet with an 'example' of how this data might look like. I left the appropriate output value in the output "AVERAGE =" cell and put the AVERAGEIFS function that I am currently using just below it.