Forum Discussion

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

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

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.00	536.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.00	353.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.00	223.25
1/22/2023	$0.00
1/23/2023	$89.91	313.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.00	328.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.00	536.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.00	353.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.00	223.25
1/22/2023	$0.00
1/23/2023	$89.91	313.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.00	328.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
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
  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.
  - radial2021
    Copper Contributor
    May 19, 2023
    @Quadruple_Pawn
    Good Morning,
    I have another formula problem and I do not know why, see below
    
    I do not see any text issues? the answer I should get is $1,869.14(B23)
    Thank you