3 Methods to Sum the Values of the Same Cells in Multiple Worksheets (Part II)

In our previous article, we have introduced the 2 methods of 3-D reference. And here we will discuss the third method to sum values.

The last way is using the pivot table in Excel. And you need some extra steps to create such a pivot table that can also sum values.

Method 3: Add the Button

  1. Click the small arrow in the quick access toolbar.
  2. Then click “More Commands” in the ribbon.Click More Commands
  3. In the “Choose commands from”, select the “All Commands”.
  4. Then drag the scrollbar until you see the “PivotTable and PivotChart Wizard” option.Add PivotTable Wizard
  5. Next click the button “Add”.
  6. And then click the button “OK”. Therefore, you have added it in the quick access toolbar.

The Button in Toolbar

Create Pivot Table

  1. Click the button that you have added in the toolbar.
  2. In the wizard, choose the option “Multiple consolidation ranges”. Here because we will use several worksheets.Select Multiple Consolidation Ranges
  3. And then click “Next”.
  4. In the next window, choose the second option.Choose Second Option
  5. And then you will see another new window. Input the range of the first sheet into the text box: ‘2014’!$A$1:$C$10 . You can also use your mouse to select the range directly.
  6. Then click the button “Add”.Add Range
  7. In the next part, choose the option “1”.
  8. And in the “Field one” text box, input the name of the first worksheet.
  9. And then repeat the step 11-14 to add the other several worksheets.Add all Range
  10. After that, click “Next”.
  11. And then you will see a new window. You can choose to put the pivot table in a new worksheet, or you can also input in the existing sheet. And you can designate a specific worksheet. Here we choose the “New worksheet”.Click Finish
  12. And then click “Finish”. Thus, the pivot table will be created.The Pivot Table

It has already been sorted and you can get all the total values quickly. What’s more, in the page option, you can also select and check the values in a specific worksheet. That’s because you have input the name of worksheets in “Filed one” text box.Select Page

The 3 methods above have different advantages. And you can use one of them to sum values according to your actual need.

Excel Errors should not be Ignored even if they Seem Unimportant

Sometimes you will meet with some Excel errors. Among those errors, although they do no harm to your data and information, you still need to be careful. That’s because they might be the precursor of a severe data disaster in some circumstance. And once such accident happens, you will face the danger of losing all your precious materials. Hence you can turn to our sophisticated tool to fix Excel files. It is well-known in the world and owns a lot of fans due to its powerful capacity.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com