Forum Discussion
Question regarding sum a range of cells
Thanks for your response. Although I don't want the formula in cell B2 to sum the same values as cell A2.
In cell A2 I want to sum the first seven days of the year (i.e. cell B2-H2 from sheet 'sales per day') and in cell B2 I want to sum the next seven days of year (i.e. cell I2-O2 from sheet 'sales per day') and in cell C2 I want to sum the next seven days of the year (i.e. cell P2-V2 from sheet 'sales per day').
You can use maybe the OFFSET function in combination with the SUM function to achieve the desired result.
In cell A2 of the 'sales per week' sheet, you can enter the formula
=SUM(OFFSET('sales per day'!$B$2,0,(ROW()-2)*7,1,7)) .
This formula will sum the first 7 cells in row 2 of the 'sales per day' sheet.
When you copy this formula down to cell B2, it will automatically update to sum the next 7 cells in row 2 of the 'sales per day' sheet.
The OFFSET function takes 5 arguments: reference, rows, cols, height and width.
In this case, the reference is 'sales per day'!$B$2 which is the first cell you want to sum.
The rows argument is 0 because you want to stay in the same row.
The cols argument is (ROW()-2)*7 which will move 7 columns to the right for each row you move down in the 'sales per week' sheet.
The height argument is 1 because you only want to sum one row of data.
The width argument is 7 because you want to sum 7 columns of data.
Another solution could be to use the INDIRECT function in combination with the SUM function.
In cell A2 of the 'sales per week' sheet, you can enter the formula
=SUM(INDIRECT("'sales per day'!"&ADDRESS(2,(ROW()-1)*7+1)&":"&ADDRESS(2,(ROW()-1)*7+7))) . This formula will sum the first 7 cells in row 2 of the 'sales per day' sheet.
When you copy this formula down to cell B2, it will automatically update to sum the next 7 cells in row 2 of the 'sales per day' sheet.
The INDIRECT function takes a text string that represents a cell reference and returns the value of that cell. In this case, we use the ADDRESS function to create a text string that represents the range of cells we want to sum. The first argument of the ADDRESS function is the row number and the second argument is the column number. We use (ROW()-1)*7+1 and (ROW()-1)*7+7 to calculate the column numbers for the range we want to sum.
I hope this helps!
NikolinoDE Hi! Thanks for your reply!
I tried this formula: =SUM(OFFSET('sales per day'!$B$2,0,(ROW()-2)*7,1,7)) and it worked for cell A2 but not if I copied the cell to B2 and C2.I don't know if the attached screenshots help you understanding my problem?
One uses ROW() if the formula is to be copied down. Since you copy to the right, you need to use COLUMN(). And since the first formula is in the first column, use COLUMN()-1
=SUM(OFFSET('sales per day'!$B$2,0,(COLUMN()-1)*7,1,7))
- Hi Hans! Thanks alot for your reply.
I'm trying to use this formula to my actual data set now but can't manage to get it right.
The range I want to sum for week 1 2023 is here:
Sheet: 13 week forecast
Cell: CCY12:CDE12
The cell I want the sum for week 1 in:
Sheet: Week-to-week
Cell: BD4
The range I want to sum for week 2 2023 is here:
Sheet: 13 week forecast
Cell: CDF12:CDL12
The cell I want the sum for week in:
Sheet: Week-to-week
Cell: BE4
Which formula should I use in cell BD4 (sheet Week-to-week) and which formula should I use in cell BE4 (sheet Week-to-week)?
