How to Sum Same Cell in Multiple Sheets in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

how to sum same cell in multiple sheets in excel


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.

consolidate feature to sum same cell in multiple sheets in excel

  • 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.

consolidate window to sum same cell in multiple sheets in excel

  • 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:
=Jan!C5+Feb!C5

manual formula to sum same cell in multiple sheets in excel

  • 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


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:
=SUM(Jan!C5,Feb!C5)

SUM formula to sum same cell in multiple sheets in excel

  • 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:
=SUM(Jan:Feb!C5)

range specify to sum same cell in multiple sheets in excel

  • 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

Nazmul Hossain Shovon
Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo