Forum Discussion

radial2021's avatar
radial2021
Copper Contributor
May 11, 2023
Solved

formula

How do I add 7 rows and then take that information and get the sum of that information in different columns in the 7 rows. Once I have that information I want to be able to copy and paste that inform...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    May 16, 2023

    radial2021 

    =SUM(OFFSET(B2,(ROW(1:1)-ROW($1:$1))*6,0,7,1))

    Does this formula help? It sums the values from row 2 to row 8 and then from row 9 to row 15 and then row 16 to 22 and so on. I think you can easily adapt it to your sheets if it returns the intended result.

     

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
May 12, 2023

radial2021 

To sum the values in different columns for a range of 7 rows and then copy the formula down for subsequent ranges, you can use the OFFSET function along with the SUM function.

Here's an example formula that you can use:

=SUM(OFFSET(BM2,(ROW()-2)*7,0,7,1))

This formula will start summing at the cell BM2, and then offset by 7 rows for each subsequent range. The ROW() function returns the current row number, so by subtracting 2 (since the formula starts in row 2), multiplying by 7 (since there are 7 rows in each range), and then adding that value to the starting cell reference, the formula will sum the correct range for each subsequent row.

To copy and paste the formula down for subsequent ranges, simply select the cell with the formula, and then drag the fill handle (the small square at the bottom right corner of the cell) down to the next cell where you want to paste the formula. The formula will automatically adjust to sum the next range of 7 rows.

 

*The formula you provided is correct.

 

I hope this helps! 

  • radial2021's avatar
    radial2021
    Copper Contributor
    May 16, 2023

    NikolinoDE

    Thank you for the information, I put the new formula and for the first 2 lines it worked by then the 3rd line it skipped 2 rows.  see below (information followed by picture of formula, and then the error

    Thank you for your help with this

     

    1/1/2023$0.00 
    1/2/2023$0.00 
    1/3/2023$147.97 
    1/4/2023$149.09 
    1/5/2023$139.85 
    1/6/2023$99.71 
    1/7/2023$0.00536.62
    1/8/2023$0.00 
    1/9/2023$87.71 
    1/10/2023$89.91 
    1/11/2023$81.35 
    1/12/2023$49.46 
    1/13/2023$45.05 
    1/14/2023$0.00353.48
    1/15/2023$0.00 
    1/16/2023$0.00 
    1/17/2023$77.51 
    1/18/2023$69.44 
    1/19/2023$39.83 
    1/20/2023$36.47 
    1/21/2023$0.00223.25
    1/22/2023$0.00 
    1/23/2023$89.91313.16
    1/24/2023$82.83 
    1/25/2023$49.50 
    1/26/2023$54.89 
    1/27/2023$51.51 
    1/28/2023$0.00328.64
    1/1/2023$0.00 
    1/2/2023$0.00 
    1/3/2023$147.97 
    1/4/2023$149.09 
    1/5/2023$139.85 
    1/6/2023$99.71 
    1/7/2023$0.00536.62
    1/8/2023$0.00 
    1/9/2023$87.71 
    1/10/2023$89.91 
    1/11/2023$81.35 
    1/12/2023$49.46 
    1/13/2023$45.05 
    1/14/2023$0.00353.48
    1/15/2023$0.00 
    1/16/2023$0.00 
    1/17/2023$77.51 
    1/18/2023$69.44 
    1/19/2023$39.83 
    1/20/2023$36.47 
    1/21/2023$0.00223.25
    1/22/2023$0.00 
    1/23/2023$89.91313.16
    1/24/2023$82.83 
    1/25/2023$49.50 
    1/26/2023$54.89 
    1/27/2023$51.51 
    1/28/2023$0.00328.64

    instead of taking rows 15-21(date 1/15/23-1/21/23 it took 17-23(date 1/17/23-1/23/23

     

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      May 16, 2023

      radial2021 

      =SUM(OFFSET(B2,(ROW(1:1)-ROW($1:$1))*6,0,7,1))

      Does this formula help? It sums the values from row 2 to row 8 and then from row 9 to row 15 and then row 16 to 22 and so on. I think you can easily adapt it to your sheets if it returns the intended result.

       

      • radial2021's avatar
        radial2021
        Copper Contributor
        May 17, 2023

        OliverScheurich 

        This formula works, 

        Thank you very much for your help with this, it will save me a lot of time.