How to sum cells across multiple sheets in Excel

In this article, we will show you how to sum cells across multiple sheets in Excel. Microsoft Excel is a great spreadsheet program used to manage data. While managing data, sometimes, we need to add values across different cells. In this situation, you can use this guide.

How to sum cells across multiple sheets in Excel

While summing cells across multiple sheets in Excel, you may come across the following two cases:

• Adding single-cell values
• Adding values in the range of cells

We will cover both of these methods here.

Adding single cell values across multiple sheets in Excel

We have created sample data containing sales of 6 different companies in four consecutive months (from January to April). The Total Sales sheet represents the total number of sales in all four months of each company.

To sum data in all sheets, use the following formula:

=SUM(first sheet name:last sheet name!cell address)

In this formula, the colon indicates the range of sheets. In our case, we are going to sum values in all sheets for different companies. Hence, the formula will be:

=SUM(January:April!B2)

If your sheet name contains a space, like Sheet 1, you have to type the sheet name under single quotation marks. For example:

=SUM('Sheet 1:Sheet 4'!B2)

In the above formula, cell B2 represents the sales of company A. Type the correct cell address, otherwise, you will receive an error.

Alternatively, you can also try this.

1. Select the sheet in which you want to display the sum.
2. Select the targeted cell and type =SUM(.
3. Now, select the first sheet containing data. Click on the cell containing a value.
4. Press and hold the Shift key and select the last sheet. Make sure that the same cell is selected there. This will select all the sheets in your Excel file.
5. Click on the formula bar and close the bracket.
6. Press Enter.

When you are done, use the Fill Handle to fill the formula in all the cells.

Adding single cell values across selected sheets in Excel

If you want to add values across some selected sheets in Excel, you cannot use the above formula because it contains the colon. In this case, you have to use commas to separate different sheets that you want to add.

in this case, you can use the following formula:

=SUM(sheet 1!cell address, sheet 2!cell address, sheet 3!cell address, ...)

For example, in our case, we want to show the total sales of different companies in January and March, the formula will be:

=SUM(January!B2, March!B2)

In the above formula, B2 represents the cell address. If your sheet name has a space, type their names under single quotations, such as:

=SUM('sheet 1'!B2, 'sheet 3'!B2)

Alternatively, you can also follow the steps provided below:

1. Select the sheet in which you want to display the sum.
2. Select the targeted cell and type =SUM(.
3. Select the first sheet and then select the required cell.
4. Click on the formula bar, type a comma, and press the spacebar.
5. Now, select another sheet and select the desired cell. Do this process until you select the cells in all the required sheets.
6. Click on the formula bar and close the bracket.
7. Hit Enter.

When you are done, use the Fill Handle to copy the formula to all the cells.

Adding values in the range of cells across multiple sheets in Excel

If your data contains multiple values in different cells in different sheets, you can also add them by selecting the range of cells. To explain this, we have created another sample data containing marks of students in different subjects in different semesters.

To sum data in the range of cells across different sheets, use the following formula:

=SUM(first sheet name:last sheet name!cell range)

In our case, if we want to add marks in all subjects in all semesters, then the formula will be:

=SUM('Semester 1:Semester 4'!B2:E2)

In the above formula, Semester 1:Semester 4 represents the range of sheets and B2:E2 represents the range of cells. We have used single quotations in the formula because our sheet names contain a space.

Adding values in the range of cells across selected sheets in Excel

To sum values in the range of cells across the selected sheets in Excel, use the following formula:

=SUM(sheet 1!cell range, sheet 2!cell range, sheet 3!cell range, ...)

Suppose we want to display the total marks of students in different subjects in semester 2 and semester 4, the formula will be:

=SUM('Semester 2'!B2:E2, 'Semester 4'!B2:E2)

The easiest way to do this is to select the range of cells in each sheet separately. If you add the range of cells in all the sheets, you can use the Shift key. We have already explained the steps to do this earlier in this article. When you are done, use the Fill Handle to copy the formula to the remaining cells.

How do I add multiple rows in a sum?

You can add multiple rows in Excel by using the SUM formula. The structure of the formula will be =SUM(cell range of first row, cell range of second row, cell range of third row, …). Alternatively, you can select the range of cells in different rows with your mouse and separate them with commas.

How do I add a sum of multiple rows in Google Sheets?

The formula to add multiple rows in Google Sheets is the same as the formula used in Excel. Type =SUM(cell range of first row, cell range of second row, cell range of third row, …) and hit Enter. Google Sheets will display the sum.

Read next: How to remove Formula in Excel and keep Text.