Forum Discussion

AmyInman's avatar
AmyInman
Copper Contributor
Sep 11, 2023

Adding consecutive cells

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?

 

 

excel

3 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Sep 11, 2023

    AmyInman 

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

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

     

  • AmyInman's avatar
    AmyInman
    Copper Contributor
    Sep 11, 2023

    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.

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Sep 11, 2023

      AmyInman 

      =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.

       

Resources