Using SUMIFS with Weekday code or Alternative.....

[madmiddle]

Afternoon folks,

I created a spreadsheet to monitor my time at work and also the overtime. now the rates vary from weekday to the weekend so I was trying to use a SUMIFS to count the weekday over time i had done in a certain period (usually 4 week blocks) and then another one to count the weekend overtime.

Now i have got part of this working with the following code:

=SUMIFS('Flexi Planner'!N4:N402,'Flexi Planner'!C4:C402,">="&'Core Hours & Dates'!G3+1,'Flexi Planner'!C4:C402,"<="&'Core Hours & Dates'!G4)

but trying to add the last criteria i'm struggling with and can't seem to get it working:

=SUMIFS('Flexi Planner'!N4:N402,'Flexi Planner'!$C4:$C402,WEEKDAY('Flexi Planner'!$C4:$C402,2)&">5")

Can you use the WEEKDAY function within a SUMIFS function, and if it doesn't how else can i do this.


Flexi Planner N4:N402 = if I have gained any overtime then it will appear in this column
Flexi Planner C4:C402 = Dates of the year starting from 02/01/2017 as that was the first monday of the year


Thank you in advance for looking

mad

 

[Jonmo1]

Sumifs can't manipulate the criteria range, it can only evaluate it exactly as it exists in the cells.
Try a helper column.
In say D4 and filled down for example
=WEEDKAY(C4,2)

Then use
=SUMIFS('Flexi Planner'!N4:N402,'Flexi Planner'!$D4:$D402,">5")

 

[madmiddle]

Sumifs can't manipulate the criteria range, it can only evaluate it exactly as it exists in the cells.
Try a helper column.
In say D4 and filled down for example
=WEEDKAY(C4,2)

Then use
=SUMIFS('Flexi Planner'!N4:N402,'Flexi Planner'!$D4:$D402,">5")

Thought that was the case. Thank you

 

[Jonmo1]

You're welcome.