Forum Discussion
Question regarding sum a range of cells
Hi,
I'm using Microsoft Excel Office 365 and have a spreadsheet containing two sheets, one sheet with the sales of each day and one sheet displaying the sales per week. The sheet that displays the sales per week is simply summarizes the values from the 'sales per day'-sheet.
In the 'sales per day'-sheet the values are presentet horizontally. So for week 1 the sales from Monday to Sunday are shown in cell B2-H2 and the sales for week 2 are shown in cell I2-O2.
I want to summarize the cells with a simple sum function in sheet 'sales per week' but if I sum the cells B2-H2 from sheet 'sales per day' in sheet 'sales per week' in cell A2 (=SUM('sales per day'!B2:H2)) and then copy the function to cell B2 the formula becomes =SUM('sales per day'!C2:I2) instead of =SUM('sales per day'!I2:O2) which is the function that I want.
How can I fix this?
11 Replies
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!
