To sum range of cells in the same worksheet is one of the basic concepts of Excel. What about summing values across sheets? In this article we will explain how to sum values across sheets.
Syntax
=SUM(first sheet : second sheet ! reference)
Steps
- Start with =SUM(
- Type the first sheet name 'Sheet 1:
- Type the last sheet name Sheet 3'
- Enter cell or range reference A1:B3
- Type ) and press Enter to complete the formula
How
The trick is called 3d reference that works in a similar way as range syntax. To create a 3d reference, combine 2 sheets with a colon character (:) and standard cell range references. The rest is regular cell (A1) or range (A1:B3) reference followed by an exclamation mark (!).
Sheet1:Sheet3!A1
Sheet1:Sheet3!A1:B3
This notation refers all cell or range references from Sheet1 to Sheet3. You can add another sheet between those two or remove one. Please note what is more important is the position of sheets from left to right, not their names. If the name of a sheet includes a space or a special character, combined sheets syntax should be enveloped by quote characters (').
'Sheet 1:Sheet 3'!A1
'Sheet 1:Sheet 3'!A1:B3
Also see related articles how to sum filtered values in data tables, and how to sum values if data is greater than.
