Forum Discussion
Need assistance to correct a formula
I am using the following formula to calculate weekly hours. I want to change it to calculate the hours with the starting on Monday going to Sunday and display the result in column G on the Sunday. For example - calculate totals from Monday Jan 6 to Sunday Jan 12, inclusive.
Thanks in advance for your help.
=IF(WEEKDAY(B6)=7, IF(SUMIFS(D:D,B:B, ">="&B6-6,B:B, "<="&B6)>0, SUMIFS(D:D,B:B, ">="&B6-6,B:B, "<="&B6), ""), "")
| A | B | C | D | E | F | G | |
| 1 | Date | Hours | Purchases | Rate | Daily Cost | Hrs / wk | |
| 2 | 1-Jan | Wed | 5 | $20.00 | $100.00 | ||
| 3 | 2-Jan | Thu | 5 | $0.00 | |||
| 4 | 3-Jan | Fri | $20.00 | ||||
| 5 | 4-Jan | Sat | $20.00 | 10.00 | |||
| 6 | 5-Jan | Sun | 7 | $20.00 | $140.00 | ||
| 7 | 6-Jan | Mon | $20.00 | ||||
| 8 | 7-Jan | Tue | $20.00 | ||||
| 9 | 8-Jan | Wed | $20.00 | ||||
| 10 | 9-Jan | Thu | $20.00 | ||||
| 11 | 10-Jan | Fri | $20.00 | ||||
| 12 | 11-Jan | Sat | $20.00 | 7.00 | |||
| 13 | 12-Jan | Sun | 3 | $20.00 | $60.00 |
2 Replies
You could use the Offset function.
You know that if the Row is a Sunday, you want the sum of hours for that row and the 6 rows before it.
=SUM(OFFSET(C12,0,0,-7,1))
In the example above C12 is the cell of interest that falls on a Sunday, it is the starting point of our Sum range and so 0,0 means we start 0 rows and 0 columns away from it, we want that row and the 6 rows before it so our height is -7 and we only want that column so the width is 1.
It could also be written (C12,-7,0,7,1) and get the same result.
Next embed into an If statement so that it only comes into effect if the date is a Sunday. Similar to as you have done already but we can just use the date value from the A column for the calculation:
=IF(WEEKDAY(A12,2)=7,SUM(OFFSET(C12,0,0,-7,1)),””)
The second argument in the Weekday function “2” establishes Monday as 1 and Sunday as 7.
The only issue with this solution is that if there are not 7 rows prior for the offset to sum then it will return #REF! error.
The only way I can think to mitigate is to use an IFERROR and CELL to get the row number of the cell to use in the calculation instead:
=IF(WEEKDAY(A2,2)=7,IFERROR(SUM(OFFSET(C2,0,0,-7,1)),SUM(OFFSET(C2,0,0,CELL(”row”)*-1,1))),””)
Btw, in the original example, if the Jan dates of column A correspond to 2026 then the Days of the week of column B are out a day.
=IF(B2="Sun",IFERROR(SUM(INDEX($C$2:C2,LARGE(IF($B$2:B2="Mon",ROW($B$2:B2)-1),1)): INDEX($C$2:C2,LARGE(IF($B$2:B2="Sun",ROW($B$2:B2)-1),1))), SUM(INDEX($C$2:C2,1):INDEX($C$2:C2,LARGE(IF($B$2:B2="Sun",ROW($B$2:B2)-1),1)))),"")This formula works in my sample file in Excel 2013 and in modern Excel. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2024. The formula is in cell I2 and filled down.
