How to Sum Same Cell in Multiple Sheets in Excel

In this tutorial, I am going to share with you 4 easy methods to sum the same cell in multiple sheets in excel. These methods can help you a lot to quickly create data summaries and save a lot of time. Also, throughout this tutorial, you will learn some valuable techniques and functions which will be helpful in other excel related tasks.

Download Practice Workbook

You can download the practice workbook from here.

4 Easy Methods to Sum Same Cell in Multiple Sheets in Excel

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 6 rows and 2 columns. We have 2 worksheets for the sales data for January and February. But the methods in this tutorial should work the same for more worksheets as well.

1. Using Consolidate Feature

The Consolidate feature in excel allows a user to combine data from multiple worksheets into one place. This can help to easily generate summaries. Below we will see how we can use this feature to sum the same cell in multiple sheets.

Steps:

• First, click cell B5 on the sheet where you want to sum the data.
• Then, go to the Data tab and select Consolidate under Data Tools.

• Now, in the Consolidate window, set SUM as Function and click on Reference.
• Next, click on the first sheet tab which has the name Jan in my case.
• Here, select the cells from B5 to C10 and click Add.

• Similarly, click on the other sheet, select cells B5 to C10, and then click Add.
• At last, check the option Left column and click OK.

• As a result, this will consolidate all the data from the previous sheets into the current sheet and sum them.

Read More: How to Sum Selected Cells in Excel (4 Easy Methods)

2. Manually Sum Same Cell in Multiple Sheets in Excel

We can use the + operator in excel to manually sum the same cell in multiple sheets. Although this is somewhat lengthy, it still helps a lot to understand the process better. Let us see how we can do this.

Steps:

• To begin with, navigate to cell C5 and insert the following formula:

• Next, press the Enter key and this will sum the values from the other sheets.
• Finally, copy this formula to the cells below using Fill Handle.

Read More: Shortcut for Sum in Excel (2 Quick Tricks)

Similar Readings

• How to Sum by Group in Excel (4 Methods)
• Sum to End of a Column in Excel (8 Handy Methods)
• How to Sum Columns in Excel (7 Methods)
• Sum by Font Color in Excel (2 Effective Ways)
• How to Sum Filtered Cells in Excel (5 Suitable Ways)

3. Applying SUM Function

The SUM function in excel gives the sum of the values that it takes. We can use numbers, cell references, ranges, arrays, etc as input of this SUM function. It can take up to 255 parameters. We will use this function to sum up the same cell in multiple sheets.

Steps:

• First, go to the cell C5 and enter the formula below:

• Now, press Enter and copy this formula to all the cells below.
• Consequently, you should get the sum of all the cells from the previous sheets.

Read More: [Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)

4. Specifying Sheet Range to Sum Same Cell

We can sum the same cell in multiple sheets in excel by specifying the range of existing sheets inside the SUM function. Follow the steps below to do this.

Steps:

• To start with this method, navigate to cell C5 and type in the following formula:

• Now, press the Enter key and copy this formula to other cells in the column.

Read More: How to Sum Between Two Numbers Formula in Excel

Things to Remember

• The datasets should have the same format in all the sheets.
• The Consolidate feature will still work if there are different left columns in the datasets.
• Empty cells and text values are automatically disregarded by the SUM function.
• You can use the Consolidate feature for other operations as well such as count, average, max, min, etc.

Conclusion

I hope that you were able to apply the above methods to sum the same cell in multiple sheets in excel.  As you can see, there are quite a few ways to achieve this task. So you can choose the one that best suits your needs. You should try to change the data format that we used in this tutorial and experiment with various types of formats. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.

Related Articles

• How to Sum Only Visible Cells in Excel (4 Quick Ways)
• Sum Only Positive Numbers in Excel (4 Simple Ways)
• How to Calculate Cumulative Sum in Excel (9 Methods)
• Calculate Sum of Squares in Excel (6 Quick Tricks)
• How to Sum Multiple Rows in Excel (4 Quick Ways)
• Vlookup and Sum Across Multiple Sheets in Excel (2 Formulas)
• How to Add Multiple Cells in Excel (6 Methods)