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.

Try Cell E29 Array Formula

Sorry, the forum firewall blocked me and prevented me from posting the formula, so I had to upload the formula in the attachment

This worked FLAWLESSLY! I even tested it with a single cell and it counted it as well in the average. I modified the dates to DATE("year cell",1,1) and DATE("year cell"+1,1,1) in case anyone else wants to use this and wants the ability to ACTUALLY change the year lol.

Thanks, again!

@sonyxmase You're Welcome. Glad to help . Thank You for the feedback