Forum Discussion
SUM part of SEQUENCE spill range
Nairn1150 Easiest would be to insert a column with the ISO-week number, using =ISOWEENUM(A2#). Then, you could do something like this:
File attached.
Nairn1150 Easiest would be to insert a column with the ISO-week number, using =ISOWEENUM(A2#). Then, you could do something like this:
File attached.
Thank you for the quick reply, I like this a lot, it's very clean compared to what I was trying to do!
I initially had the same problem when using your formula. For a while I couldn't understand what I was doing wrong when implementing your approach, but I realized that it was still trying to use a spill reference (#) for the If(Cell#="Sunday"... Once I changed it to If(Cell="Sunday"... and filled that down the column everything worked as expected.
Out of curiosity I adapted my original formulas to the same style, and ditched the spill references.
=IF(WEEKDAY(A2,2)=7,IF(DAY(A2)<7,SUM(OFFSET(A2,0,3,-DAY(A2))),SUM(OFFSET(A2,0,3,-7))),"")
This formula worked as intended, and I was able to use not just the SUM(OFFSET), but also dynamically adjust the size of the offset for split weeks by using the DAY function to set the offset range. I can't believe how close I have been skirting around this solution, and the only part letting me down was the spill range!
I wonder if my original effort is just not an intended use-case for the spill operator? I could certainly think of other uses for summing part of a spilled range, but perhaps it would require the use of helper columns, alternate functions or sum conditions etc.
In any case, thank you very much for your assistance.