Excel (Formula/VBA) Help: Counting data on 2 separate sheets

Hello,

My absence tracker records each occasion of absence using a new row. So, if an employee was sick on 04/05/2022 and sick again on 09/06/2022, the employee would have 2 rows containing absence, as it would be 2 separate occasions.

I have three sheets: "current 6 months" (absence is manually added here daily), "previous 6 months" and "last 12-24 months". Each new day, a piece of VBA code automatically moves the row to the relevant sheet upon workbook open, based on absence start date and deletes if older than 24 months.

I need to be able to see occasions of absence over the last 12 months, so basically how many occasions over the 2 sheets: "Current 6 months" and "previous 6 months".

I would need a separate sheet for this called something like "occasions last 12 months".

Each row (occasion) begins with the employee ID in column A, so a formula like:

Formula:

Please Login or Register  to view this content.

could pull unique employee IDs from the 2 sheets into column A, then something like:

Formula:

Please Login or Register  to view this content.

could count how many times each employee ID appears in the two sheets, which would tell me how many occasions. Maybe?? Not sure...

If I could just get one of each employee ID from the two sheets into column A and then count how many times each employee ID appears over the two sheets, that'd be it... but I can't do it and I've spent hours... days on it.

My novice ability with Excel unfortunately restricts me.

Hoping someone can kindly help.

Kind Regards

If the protection is removed from the master data sheet, then a column could be added that would be populated using a COUNTIFS formula (such as the one below) to count the occurrences of an employee ID on the previous and current 6 months sheets.
An Index/Aggregate formula could then be written to populate column A of the occasions last 12 month sheet.
Example:

Formula:

Please Login or Register  to view this content.

Column B could be populated using:

Formula:

Please Login or Register  to view this content.

To see the above applied to the file please remove the protection from the master data sheet.
Let us know if you have any questions.

Hello

Thanks very much for this, it works

Kind Regards
Chris

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.