This guide will explain how to sum across multiple sheets in Excel using the SUM function and 3D reference.
The rules for using the SUM function in Excel is the following:
- The
SUMfunction is used to return the sum of values we supply or select. Additionally, these selected values can be numbers, cell references, ranges, arrays, and constants in any combination. - The function can support up to 255 individual arguments.
- Furthermore, the function automatically ignores blank or empty cells and text values. However, the function will support hardcoded values and numbers that result from formulas.
- When the inputted arguments have errors, the function will return an error.
Since it has several built-in functions and tools, Excel is an excellent tool for performing difficult calculations and storing large quantities of data. With the help of Excel’s built-in functions, we can perform and manipulate the data set in different ways.
Furthermore, many people utilize Excel to perform sum calculations, especially with a large number of data. In this case, we will focus on learning how to sum across multiple sheets in Excel.
Since Excel allows us to use several sheets in one worksheet, many people use this to categorize different data sets. However, there are times when we would need to create a summary of the values from each different sheet.
Although we can simply select the values from each sheet manually, this is a really inefficient and time-consuming method. Luckily, there is a feature in Excel that would allow us to perform this task easily and efficiently.
In Excel, we can use 3D reference which is a special type of reference that includes more than one worksheet. With the use of 3D reference, we can sum across multiple sheets and return the result in one cell.
Let’s take a sample scenario wherein we need to sum across multiple sheets in Excel.
Suppose you have a class record containing the test scores of the students. Furthermore, you used different sheets to input the scores for each test. So each sheet follows the same dimensions and format. But, they only differ in the test score.
Then, you want to create a summary of the test scores for each student. To do this efficiently, you used the SUM function together with 3D reference to sum the test scores from across different sheets and place them in a summary sheet.
Before we move on to a real example of summing across multiple sheets in Excel, let’s first learn how to write the SUM function in Excel.
The Anatomy of the SUM Function
The syntax or the way we write the SUM function is as follows:
=SUM(number1, [number2])
Let’s take apart this formula and understand what each term means:
- = the equal sign is how we activate any function in Excel.
- SUM() refers to our
SUMfunction. And this function is used to add all the numbers in a range of cells. - number1 is a required argument. So this refers to 1 to 255 numbers we want to sum. Additionally, the function ignores logical values and text values including if typed as arguments.
- number2 is an optional argument. And this acts as a supplement to input more arguments up to 255 numbers that we want to sum.
Great! Now we can move on and dive into a real example of summing across multiple sheets in Excel.
A Real Example of Summing Across Multiple Sheets in Excel
Let’s say we have multiple data sets containing the test scores of 10 students. Furthermore, we have separated the scores for each test into multiple sheets. So our initial data set would look like this:
Each sheet contains the same data set that follows the same dimensions and format. And the data sets must have the same structure across multiple sheets. So the data sets all have two columns wherein the first column is 10 students while the second column contains the score for that specific test.
In this example, our goal is to sum the total test scores for each student across 3 sheets. To do this, we will simply utilize 3D reference with our SUM function. So 3D reference is an Excel feature that allows us to refer to the same cell in multiple sheets. Additionally, we can use 3D reference in functions.
However, not all functions support 3D reference. Luckily, the SUM function which we need to use to sum the total test scores supports 3D reference. Before we move on to using the 3D reference, let’s discuss the standard referencing first.
Usually, we can also sum across multiple sheets in Excel using the standard reference. But, this would entail manually referencing each cell across multiple sheets, which would simply be inefficient and time-consuming.
So standard reference would result in a formula that would look like this =SUM(Sheet1 ! B2, Sheet2 ! B2, Sheet3 ! B2)
However, 3D reference is a better method to utilize. Since this is a less complicated and efficient method, it would result in a formula that looks like this =SUM(Sheet1: Sheet3 ! B2). Moreover, this simple formula is just a shorter and simpler version of the standard reference.
Furthermore, both formulas would return the same result. But, the 3D reference would be the better method overall. So we will simply utilize the 3D reference in our SUM function to get the total test scores of each student from three different test scores found across multiple sheets.
So our final data set would look like this:
You can make your own copy of the spreadsheet above using the link attached below.
Amazing! Now we can proceed and explain the process of how to sum across multiple sheets in Excel.
How to Sum Across Multiple Sheets in Excel
In this section, we will explain the step-by-step process of how to sum across multiple sheets in Excel using the SUM function and 3D reference. Furthermore, each step has detailed instructions and pictures to help you along the way.
To apply this method to your work, simply follow the steps below.
1. Firstly, we will create a Summary sheet where we would input the sum of the test scores from across multiple sheets. To do this, we can simply click the “+” sign found beside the sheets.
2. Secondly, we will rename the new sheet. To do this, we will right-click the sheet and select Rename.
3. Alternatively, we can also double-click the sheet. Then, we will type in “Summary” and click OK.
4. Thirdly, we will create a data set following the same structure as the other data sets found in multiple sheets. Next, we can now use the 3D reference to sum the total test scores. To do this, we will simply type the SUM function and select the first sheet which is Test1.
Then, we will hold the SHIFT key and select the last sheet tab which is Test3. Afterward, we will click the first cell of the data set which is C2. So the entire formula would be “=SUM(Test1:Test3!C2)”. Lastly, we will press the Enter key to return the sum.
5. Next, we can simply drag down the Fill Handle tool to copy and apply the formula to the other cells in the data set found in the Summary sheet.
6. And tada! We have successfully summed across multiple sheets in Excel.
And that’s pretty much it! We have explained how to sum across multiple sheets in Excel using the 3D reference in the SUM function. Now you can apply this method in your work.
Are you interested in learning more about what Excel can do? You can now use the SUM function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
