Formula help. Multiple criteria, duplicates, between dates.

Hello everyone.

I have a holiday tracker which also serves other purposes.
Need a formula to sum holiday on separate (indirect function) tabs.

Formula is to use :
Month Start and End date of cycles M01 - M12 (Hol sheet K4:V5))
Start and End date in AG and AH

Formula is to meet the following criteria:
Employee ID (Hol column C; cycle column AJ)
Employee Role (Hol column E; cycle column D)
Employee Name (Hol column D; cycle column C)

If an employee had same role but changed contracted hours then the formula is to only sum holiday taken once at the most recent date range for given cycle.


I figured max function could work plus countifs but i can't seem to get me head around it.

The example i present in the attached workbook:
a) shows one employee who started as S/w role cycle 1
b) on cycle 2 the employee changed role to nurse
c) on cycle 3 the employee started two roles and took hol on both.
d) on cycle 4 the employee changed back to nurse
e) on cycle 5 the employee changed contracted hours as a nurse (here i would like the holiday taken to sum on the most recent date for cycle 5 and show the other as duplicate and nil)

Current formula

Please Login or Register  to view this content.

It uses only name and role at the moment; needs adding ID criteria.
It does not work with dates yet; that's what i need help with.
Plus needs to sum values on most recent date if all criteria met but date differs.

Is it something that can be done?

The following mega formula yields the values that are indicated by the comments as well as the other values that were already displayed:

Formula:

Please Login or Register  to view this content.

Note that the holiday value (5) for M05 will be displayed in O13 and "duplicate" will be displayed in O14
Note that on sheet M03 and M04 Smith John 2 is changed to Smith John 1.
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
I feel that you may get better/faster results if you open another thread and upload another sample file to illustrate the issue with holidays.