Table of Contents
Using the SUM function in Excel, you can quickly sum across multiple sheets by referencing the sheets in the formula. To do this, you can reference an entire sheet by typing the sheet name followed by an exclamation point and the cell range you want to sum. For example, the formula =SUM(Sheet1:Sheet3!A1:A2) will sum the contents of cells A1 and A2 across sheets Sheet1, Sheet2, and Sheet3.
You can use the following basic syntax to sum values across multiple sheets in Excel:
=SUM(Sheet1!A1, Sheet2!B5, Sheet3!A12, ...)
The following example shows how to use this syntax in practice.
Example: Sum Across Multiple Sheets in Excel
Suppose we have three sheets titled week1, week2, and week3 that each contain data about eight basketball players and their total points scored during that week:
Each sheet has the exact same layout with “Player” in column A and “Points” in column B.
Now suppose we’d like to take the sum of points scored for each player during each week and display the sum in a new sheet called total:
We can use the following formula to do so:
=SUM(week1!B2, week2!B2, week3!B2)
The following screenshot shows how to use this formula in practice:
The “Total Points” column contains the sum of the points scored for each player across week1, week2, and week3.
For example:
- Player A scored a total of 20 points across the three weeks.
- Player B scored a total of 18 points across the three weeks.
- Player C scored a total of 21 points across the three weeks.
And so on.
=SUM(week1:week3!B2)
The following screenshot shows how to use this formula in practice:
Notice that the values for the Total Points column match the ones we calculated earlier.