I have a 13 tables that are the 4 week schedules for the coming year. I would like to summarize them to show how many of each type of shift each employee has been scheduled for. Here is an example:enter image description here

This would obviously extend out 28 columns of info. Also note that many shifts have two rows as we have two workers on that shift.

One main kicker is I need to check if the date is a weekday or weekend day as those are counted differently for pay, coverage etc.

I've tried a couple of approaches and haven't really gotten anywhere though I feel this should be relatively simple. I am ok with summarizing each table individually and then summing those for a grand total if needed.

I thought to try building a summary table like so:

enter image description here

And then use countifs to compare for the employee's name in the whole range and if the shift name was found in column A and if value in the date row was a weekday but this didn't work due to different range sizes. I have seen some posts on sumproducts that at first seemed promising but then ran into the same problem.

I would like to avoid a scenario where I use a formula that is countifs(tests for row 4) + countifs(tests for row 5) to get Shift A info for instance as there may be different numbers of shifts A added overtime.

Would appreciate any tip in the right direction for what to try next.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.