How to sum same cell in multiple sheets in Excel?
When working with Excel workbooks that contain multiple worksheets, a common need is to calculate the total of values found in the same cell—such as cell A2—across all or several sheets. Rather than moving each value manually into a single column to sum them up, which can be extremely time-consuming and error-prone for large workbooks, there are a number of methods in Excel to quickly and accurately sum the same cell from multiple sheets.
Below, you’ll find several solutions—each with their own advantages, drawbacks, and best-fit scenarios. Choose the one that best matches your needs, whether you prefer formulas, Excel features, add-ins, or even VBA code:
Sum same cell in multiple sheets with a formula
Sum same cell in multiple sheets with Kutools for Excel
Sum same cell in multiple sheets using Excel Consolidate feature
Sum same cell in multiple sheets with a formula
Excel provides a built-in formula approach to automatically sum up the same cell across several or all worksheets. This method is fast and doesn’t require any add-ins or special setup, making it suitable for users who are comfortable with formulas and who want a live, dynamically updating result.
Select a blank cell where you want to display the result, then enter the following formula:
=SUM(Sheet1:Sheet7!A2)Press the Enter key, and you will see the sum of the values from cell A2 across all worksheets from Sheet 1 to Sheet 7 appear in the cell you selected. If any of the underlying values change, the total updates automatically.
Tip:
1. In the formula above, Sheet1:Sheet7 references every worksheet between and including Sheet 1 and Sheet 7. Adjust these sheet names as appropriate for your workbook. Make sure the sheets are grouped together without any unwanted sheets in between, as all intermediate sheets are included.
2. If you need to sum the same cell in specific, non-consecutive sheets only, use a formula like:
=SUM(Sheet1!A2,Sheet2!A2,Sheet5!A2,Sheet7!A2)Enter this formula in a blank cell. The sheet names and cell references can be adjusted as needed, separating each with a comma.
3. Both formulas dynamically update if you change any values in the referenced cells. But if you delete one of the referenced sheets, you may encounter a #REF! error.
4. Always double-check sheet names for typos and ensure that every referenced sheet has the target cell present. Otherwise, the formula may return errors.
This method provides a straightforward way to aggregate data, especially when tracking similar metrics across monthly, quarterly, or project sheets.
Sum same cell in multiple sheets with Kutools for Excel
For users who need a more interactive and visual approach—especially when combining data from many workbooks or more complex scenarios—Kutools for Excel offers an efficient solution. With the Combine function, you can quickly sum the same cell across all—or just some—worksheets without manual formulas, and even summarize from multiple workbooks simultaneously. This is particularly efficient for large scale consolidation or when you want to visualize and manage the process step by step.
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 30 days. Get it Now
1. Click Kutools Plus > Combine on the ribbon.
2. In the dialog box, choose Consolidate and calculate value across multiple workbooks into one worksheet. This enables you to aggregate the same cell position across worksheets or entire workbooks. See screenshot:
3. Click Next and select the desired workbooks and worksheets from the Workbook list and Worksheet list. Then, in the Range field, select a cell (e.g., A2) from any sheet and use the Same range button to quickly apply your selection to all checked sheets. This ensures you’re summing the precise cells you need. See screenshot:
4. Click Finish. Kutools will create a new worksheet (or workbook, as specified) displaying the sum result, clearly organizing your consolidated data. See screenshot:
Tip: A dialog box will prompt you to save the scenario. Saving allows you to easily repeat the process in the future. Choose Yes to save, or No if you don’t need to reuse the configuration.
Compared to formula methods, Kutools offers a guided, flexible approach, suitable for users handling extensive or complicated consolidation tasks—such as monthly financials from many departments or project sheets from multiple sources. The interface is user-friendly, but make sure Kutools is installed prior to use. Data integrity is automatically preserved, reducing the risk of manual errors.
Click here for more information on Combine function.
Sum same cell in multiple sheets using Excel Consolidate feature
Excel’s built-in Consolidate feature allows users to aggregate data from the same cell across several sheets without writing formulas or using add-ins. This is a convenient, visual way to quickly sum identical cell positions from multiple worksheets—even from different workbooks.
Scenario: This method works best when you want fast, one-time consolidation of static data, or when you’re more comfortable working with ribbon features than writing formulas. It’s not dynamic; if your source data changes, you’ll have to repeat the process, and it’s intended for numerical (not text) summarization.
1. Prepare a new or existing worksheet where you want the consolidated result displayed.
2. Go to Data > Consolidate in the menu.
3. In the pop-up window, set the function to Sum. Then, click in the Reference field and select the same cell (e.g., A2) in each worksheet. Click Add each time to include it in the list. You can reference cells across multiple sheets and even workbooks if they’re open.
4. After adding all references, click OK. The sum result will be displayed in your chosen sheet and cell.
Tips:
- You can only summarize numeric values. Make sure each sheet has data in the referenced cells.
- You can consolidate data from other open Excel files as well, by switching windows in the reference selection step.
- This method is not automatic: If you change a value in one of the original sheets, you’ll need to run Consolidate again to update the results.
This feature is suitable for a quick summary, especially when you need a total for a snapshot report or to combine results from collaborating team members.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in