Forum Discussion
Question regarding sum a range of cells
You can fix this by using an absolute reference for the column in your formula.
In Excel, you can make a cell reference absolute by adding a dollar sign ($) before the column letter. For example, instead of using ` =SUM('sales per day'!B2:H2) `, you can use ` =SUM('sales per day'!$B2:$H2) `.
This will prevent the column reference from changing when you copy the formula to another cell.
So, in your case, you can enter the formula ` =SUM('sales per day'!$B2:$H2) ` in cell A2 of the 'sales per week' sheet and then copy it to cell B2.
The formula in cell B2 will remain ` =SUM('sales per day'!$B2:$H2) ` instead of changing to ` =SUM('sales per day'!C2:I2) `.
I hope this helps!
- Hi,
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?
Given you run 365, a dynamic array alternative (sample attached)
- Make sure A2, B2, C2... cells are free in 'sales per week' otherwise you'll get a #SPILL! error
- Assuming daily sales are in range 'sales per day'!B2:O2 (EDITED: better option):
=LET( dailysales, 'sales per day'!B2:O2, MAKEARRAY(1,COLUMNS(dailysales)/7, LAMBDA(rw,cl, SUM(INDEX(dailysales,SEQUENCE(,7,(cl*7)-6))) ) ) )
