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
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
