Adding consecutive cells

Copper Contributor

Hi all, I need to populate the below each week for all of our sites. I'm hoping there is a formula I can use that will pull 7 days of data from one worksheet to this one, without manually adding them all together. Obviously each week has a different set of 7 cells. Is this possible?




3 Replies

@AmyInman just for a bit more information if I haven't explained it properly:

I need to be able to get the info from cells E158-E164 from one worksheet into 1 cell on the main worksheet (Z136), then for the following week it would need to pull cells E165-E171 to cell AA136 and so forth.


=TEXTJOIN(" ",,OFFSET('one worksheet'!$E$158:$E$164,(COLUMN(A1)-COLUMN($A$1))*7,0))

If you have access to TEXTJOIN you can try this formula in cell Z136 and fill it to the right.



Summing the amounts  is more commonly encountered than listing them.  That would require

= SUMIFS(Amount,
       Category, distinctCategory,
       Date,     ">=" & weekCommencing,
       Date,     "<"  & weekCommencing + 7