How to sum cells in multiple worksheets in Excel

This article will demonstrate how to sum cells in multiple worksheets in Excel. Microsoft Excel is a powerful spreadsheet program used for data management. When working with data, you may need to sum across multiple cells. This guide will show you how to achieve this easily.

How to sum cells in multiple worksheets in Excel

When summing cells in multiple worksheets in Excel, you may encounter the following two situations:

• Add a single cell value
• Add values ​​in cell range

We will introduce both methods here.

Add a single cell value across multiple worksheets in Excel

We collected sample data containing the sales of 6 different companies in four consecutive months (from January to April). The total sales table includes each company's total sales during the four-month period.

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

=SUM(第一张图纸名称：最后一张图纸名称！单元格地址)

In this formula, the colon indicates the range of the plate. In our example, we will sum the values ​​from all tables for different companies. Therefore, the formula will be:

=SUM(1月：4月B2)

If the drawing name contains spaces, such as Drawing 1, the drawing name must be typed under single quotes. For example:

=SUM(‘Sheet 1：Sheet 4’！B2)

In the above formula, cell B2 represents the sales volume of company A. Please enter the correct cell address or you will receive an error message.

Alternatively, you can try this.

Select the worksheet in which you want to display the sum.

Select the target cell and type =SUM(.

Now, select the first worksheet that contains the data. Click the cell containing the value.

Hold down the Shift key and select the last worksheet. Make sure the same cell is selected there. This will select all worksheets in your Excel file.

Click on the formula bar and close the brackets.

Press Enter.

When finished, use the fill handles to fill the formula in all cells.

Add single cell value in selected worksheet in Excel

If you want to add the values ​​in some selected worksheets in Excel, you cannot use the above formula because it contains colons. In this case, you must use commas to separate the different worksheets you want to add.

In this case you can use the following formula:

=SUM(表%1！单元格地址，表%2！单元格地址，表%3！单元格地址，...)

For example, in our example, we want to display the total sales of different companies in January and March, the formula is:

=SUM(1月B2，3月B2)

In the above formula, B2 represents the cell address. If there are spaces in the drawing name, type its name within single quotes, for example:

=SUM(‘Sheet 1’！B2，‘Sheet 3’！B2)

Alternatively, you can follow these steps:

Select the worksheet in which you want to display the sum.

Select the target cell and type =SUM(.

Select the first worksheet and then select the desired cells.

Click the formula bar, type comma, and then press the space bar.

Now, select another worksheet and select the desired cells. Follow this process until all desired cells in the worksheet are selected.

Click on the formula bar and close the brackets.

Press Enter.

When finished, use the fill handles to copy the formula to all cells.

Add values ​​in a cell range in multiple worksheets in Excel

If your data contains multiple values ​​in different cells in different worksheets, you can also add these values ​​by selecting a range of cells. To explain this, we created another sample data containing the grades of students in different subjects in different semesters.

To sum data in a range of cells on different worksheets, use the following formula:

=SUM(第一个工作表名称：最后一个工作表名称！单元格区域)

In our example, if we want to add points in all subjects in all semesters, the formula will be:

=sum(‘第一学期：第四学期’！B2：E2)

在上式中，学期1：学期4表示片材的范围，B2：E2表示单元格的范围。我们在公式中使用了单引号，因为我们的工作表名称包含空格。

在Excel中的选定工作表的单元格区域中添加值

要在Excel中对选定工作表的单元格范围内的值求和，请使用以下公式：

=SUM（表1！单元格范围，第二页！单元格范围第三页！单元格范围，.）

假设我们要显示第二学期和第四学期不同科目学生的总分，公式为：

=sum(‘学期2’！B2：E2，‘学期4’！B2：E2)

最简单的方法是分别选择每个工作表中的单元格范围。如果在所有工作表中添加单元格区域，则可以使用Shift键。我们已经在本文前面解释了执行此操作的步骤。完成后，使用填充手柄将公式复制到剩余的单元格。

如何在求和中添加多行？

您可以使用求和公式在Excel中添加多行。公式结构为=SUM(第一行单元格区域，第二行单元格区域，第三行单元格区域，…)。或者，您可以用鼠标选择不同行中的单元格范围，并用逗号分隔它们。

如何在Google Sheets中添加多行的总和？

在Google Sheets中添加多行的公式与在Excel中使用的公式相同。类型=SUM(第一行单元格范围，第二行单元格范围，第三行单元格范围，…)然后按Enter键。Google Sheets将显示总和。

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

The above is the detailed content of How to sum cells in multiple worksheets in Excel. For more information, please follow other related articles on the PHP Chinese website!