Function Problems

Add the Same Cell from Multiple Excel Worksheets to a Summary Sheet

Steven ParkerSteven Parker Nov 18, 2026 868 views

Question details

Combine the corresponding cell from five worksheets on a summary sheet, either by adding numbers or joining text.

Add the Same Cell from Multiple Excel Worksheets to a Summary Sheet
Product
Microsoft Excel
Device & OS
not provided
Scenario
A workbook has five source worksheets and a summary sheet that should display a combined result for each corresponding cell.
Observed behavior
The summary sheet should show either the numeric total or the combined text from the matching cells on the five worksheets.
Before you start

Check the names of the five source worksheet tabs and decide whether you need a numeric total or joined text. The examples use Table1 through Table5 and cell A1; replace these names and references with your own.

Solution 1Recommended

Sum matching cells from five Excel worksheets

Use SUM to add numeric values from the same cell on each source sheet.

1
Select the summary cell

Open the summary worksheet and click cell A1, or the cell where you want the first total.

2
Enter the SUM formula

In the selected cell, enter =SUM(Table1!A1,Table2!A1,Table3!A1,Table4!A1,Table5!A1) and press Enter.

3
Fill the formula

Drag the cell's fill handle across and down to calculate totals for the corresponding cells in the other worksheets.

Sum matching cells from five Excel worksheets
Use a 3D reference for consecutive sheets: If Table1 through Table5 are consecutive worksheet tabs, you can use =SUM(Table1:Table5!A1) instead. This includes every sheet between the two named tabs, so keep the summary sheet outside that range.
Combine worksheet cells in WPS

Create a Multi-Sheet Summary in WPS Spreadsheet

Use cross-sheet formulas in WPS Spreadsheet to add numbers or join text from matching cells on a summary sheet.

  1. 1. Open the workbook: Open WPS Spreadsheet and select Open to load the workbook containing the source worksheets.
  2. 2. Choose a summary cell: Click the summary worksheet tab, then select cell A1 or your preferred starting cell.
  3. 3. Enter a formula: For numbers, enter =SUM(Table1!A1,Table2!A1,Table3!A1,Table4!A1,Table5!A1). For text, use CONCATENATE with the same cell references. Replace the example sheet names as needed and press Enter.
  4. 4. Fill the summary range: Drag the formula cell's fill handle across and down to combine the corresponding cells from each source worksheet.
Summarize matching cells from multiple worksheetsOpen and edit Microsoft Excel XLS and XLSX filesFill formulas across rows and columnsChoose SUM for numbers or CONCATENATE for text
microsoft office alternative - wps office

Frequently Asked Questions

How do I add the same cell from several Excel sheets?

On the summary sheet, enter a SUM formula such as =SUM(Table1!A1,Table2!A1,Table3!A1) and include each source sheet you need.

When can I use a 3D reference instead of listing every sheet?

Use a formula such as =SUM(Table1:Table5!A1) when the source sheets are consecutive tabs. Excel includes every worksheet between Table1 and Table5 in the total.

How do I add spaces or commas between combined text values?

Insert separators as text arguments in the formula. For example, =CONCATENATE(Table1!A1,", ",Table2!A1) puts a comma and space between the two values.

What if a worksheet name contains spaces?

Enclose the worksheet name in single quotation marks in the formula, as in ='Sales January'!A1. Use that reference inside SUM or CONCATENATE.

Can I use the formula for an entire range?

Yes. Enter the formula in the first summary cell, then drag its fill handle across and down. Relative references such as A1 adjust to match each destination cell.