Forum Discussion
Help with changing a formula
I’ve answered more fully on your other post:
Need assistance to correct a formula | Microsoft Community Hub
With a solution using the Offset function:
=IF(WEEKDAY(A2,2)=7,IFERROR(SUM(OFFSET(C2,0,0,-7,1)),SUM(OFFSET(C2,0,0,CELL(”row”,C2)*-1,1))),””)
But in relation to the specific point you mention in this one that “Currently it calculating the values beginning on Sunday to Saturday. I would like to change it to sum from Monday to Sunday”
I think this would be because you haven’t used the optional second parameter of the Weekday function that defines which day of the week is 1 and which is 7. Without it, the default parameter is 1 which defines Sunday as 1 and Saturday as 7.
https://support.microsoft.com/en-us/office/weekday-function-60e44483-2ed1-439f-8bd0-e404c190949a
- another realisation is that your formula may already be working as desired and the calculation only appears to be doing Sun-Sat because the day of week is mislabelled for 2026 dates. ie Row 5 for Jan 4th is a Sunday and has the sum of hours but row B has it labelled Sat. Are you working with 2025 dates?
Therefore your solution does work and is quite elegant but I would refine it as follows:
=IF(WEEKDAY(A2,2)=7,SUMIFS(C:C,A:A,”>=“&A2-6,A:A,”<=“&A2),””)
And then look at how your Day of Week column B is being calculated and if 2026 dates fix that so that the correct dates are labelled Sunday - ignore this if 2025 dates.