# Excel wizards needed - Staff overtime rota planner

#### Yella Fella

Associate
Not that you need to know but let's start over as my post didn't save when I clicked back from the preview

So.... I have a staff rota that tracks the number of days someone has worked and on another sheet based on the number of days worked the number of total hours worked.

The query is regarding the former which as the formula stands, there is no issue when I span the whole row of August to whenever. Let's say December. The issue is when I try to use a month range it gives me a #VALUE error and I don't know why. As it stands this formula works which adds up the whole row:

=SUM(COUNTIFS(Day,{"Mon","Tue","Wed","Thu","Fri"},M9:CN9,">0"))

For example I just want to add up August so I'm ranging the dates 16-Aug to 31-Aug to get this formula which fails for some reason?

=SUM(COUNTIFS(Day,{"Mon","Tue","Wed","Thu","Fri"},M9:AE9,">0"))

This is how it works... I enter the hours worked e.g. 2 hours here, 4 hours there on the right grid. On the left it till tot up the number of days worked, not hours (this is worked out on another sheet which is fine). However its the total number of the days for the duration of said range e.g. August through to December.

It's split into different days for the rates so we have M-F (Mon-Fri), Sat, Sun (same as bank holiday). The reason is so I can make a fairer rota so everyone gets a shot at double time etc.

I just want to work out each section for each month as a month cost break down but I just don't get why the formula won't work in a smaller range. Can anyone explain or see what's wrong or how I can make it work please? Thank you all.

Not sure how to insert the image so this is the link for the spreadsheet redacted https://ibb.co/SfJrQh9

#### Yella Fella

Associate
OP
Additionally, I have a range called 'Day' which spans the whole date range. Is that why it's failed if I shorten it?

This is the first row that shows Sun, Mon, Tue... etc

#### Greboth

Soldato
I'm not at a PC to try it but my first thought would be the 'Day' named range. I assume that it is the column M to CN for the row that contains Mon, Tue, Wed etc. so the COUNTIF function works as it covers the same columns. Does you second formula for August work if you edit the 'Day' range to only be columns M to AE?

Soldato