We’ll show you a few ways to add cells across sheets in Excel. You can sum the values that appear in the same cell across your spreadsheets or different cells.
Sum the Same Cell Reference
If you have different sheets with identical layouts in your Excel workbook, you can sum the same cell reference across multiple sheets easily.
For example, you might have a separate product sales spreadsheet for each quarter. In each sheet, you have a total in cell E6 that you want to sum on a summary sheet. You can accomplish this with a simple Excel formula. This is known as a 3D reference or 3D formula.
Start by heading to the sheet where you want the sum for the others and select a cell to enter the formula.
You’ll then use the SUM function and its formula. The syntax is =SUM(‘first:last’!cell) where you enter the first sheet name, the last sheet name, and the cell reference.
Note the single quotes around the sheet names before the exclamation point. In some versions of Excel, you may be able to eliminate the quotes if your worksheet names don’t have spaces or special characters.
Enter the Formula Manually
Using our product sales by quarter example above, we have four sheets in the range, Q1, Q2, Q3, and Q4. We would enter Q1 for the first sheet name and Q4 for the last sheet name. This selects those two sheets along with the sheets between them.
Here’s the SUM formula:
=SUM(‘Q1:Q4’!E6)
Press Enter or Return to apply the formula.
As you can see, we have the sum for the value in cell E6 from sheets Q1, Q2, Q3, and Q4.
Enter the Formula With Your Mouse or Trackpad
Another way to enter the formula is to select the sheets and cell using your mouse or trackpad. You should then have your total in your summary sheet. If you look at the Formula Bar, you can see the formula there as well.
Sum Different Cell References
Maybe the cells you want to add from various sheets are not in the same cell on each sheet. For instance, you might want cell B6 from the first sheet, C6 from the second, and D6 from a different worksheet. Go to the sheet where you want the sum and select a cell to enter the formula. For this, you’ll enter the formula for the SUM function, or a variation of it, using the sheet names and cell references from each. The syntax for this is: =SUM(‘sheet1’!cell1+’sheet2’!cell2+’sheet3’!cell3…). Note the use of single quotes around the worksheet names. Again, you may be able to eliminate these quotes in certain versions of Excel.
Enter the Formula Manually
Using the same sheets as our initial example above, we’ll sum sheet Q1 cell B6, sheet Q2 cell C6, and sheet Q3 cell D6. You would use the following formula: =SUM(‘Q1’!B6+’Q2’!C6+’Q3’!D6) Press Enter or Return to apply the formula. Now you can see, we have the sum for the values in those sheets and cells.
Enter the Formula With Your Mouse or Trackpad
You can also use your mouse or trackpad to select the sheets and cells to populate a variation of the SUM formula rather than typing it manually. You should then be returned to the formula cell in your summary sheet. You’ll see the result from the formula and can view the final formula in the Formula Bar. Now that you know how to sum cells across sheets in Excel, why not take a look at how to use other functions like COUNTIFS, SUMIFS, and AVERAGEIFS in Excel.
