Adding counters for each employee based on cell values in multiple sheets

Hello everyone,

I'm trying to generate a counter for each employee based on cell values on multiple sheets.

I have attached a mocked-up file of what I'm looking for. Example of data is shown on the January sheet (I need the counters on the summary page to keep adding up based on the data inputted on each sheet - January, February, March and so on). A screen shot of the counter I would like to use it shown on the summary sheet. I would need a counter for each employee.

Thank you.

Employee Absence Report COPYCOPY.xlsx

Well, 10 out of 10 for artistic impression. I'm looking at this on an iPad so I can't get at your existing formulae.

I would use VSTACK to stack the data from the month sheets in one "table" and use that as the basis for your counters. You might need to use FILTER rather than COUNTIF as I think COUNTIF might need a range rather than an array.

This has been adapted from a very well-used Microsoft template - I recognise the format and style.

I wouldn't know. Never used them. Too much "pretty" and not enough practical.

The named ranges are getting in the way of stacking the worksheets with HSTACK, so would need renaming.

OK - so I renamed the named ranges from January and February to JanuaryM and FebruaryM, then used the following in February AI9:

=LET(c,HSTACK(January:February!C9:AG9),IFERROR(COLUMNS(FILTER(c,c=LEFT(FebruaryM[[#Headers],[Paid/Unpaid]]))),0))

The other monthly named ranges would need similarly renaming, then the formula for March would be:

=LET(c,HSTACK(January:March!C9:AG9),IFERROR(COLUMNS(FILTER(c,c=LEFT(MarchM[[#Headers],[Paid/Unpaid]]))),0))

and for April:

=LET(c,HSTACK(January:April!C9:AG9),IFERROR(COLUMNS(FILTER(c,c=LEFT(AprilM[[#Headers],[Paid/Unpaid]]))),0))

and so on.