How do you enter a formula using a 3D reference and the SUM function in Excel?
Learning Objectives
Show The Summary sheet in many multiple sheet workbooks is utilized to present totaled information from the other sheets in the file. This is done to give a quick synopsis of all the other sheets in one convenient location. For this reason, the Summary sheet is usually the first sheet in multiple sheet files. Summary sheets “pull” data from the other sheets using three-dimensional (3-D) cell references. In order to distinguish between A3 in the Summary sheet, A3 in the January sheet, A3 in the February sheet, etc.; a 3-D cell reference includes the sheet name along with the cell reference. The syntax to reference a cell in a different sheet is =SheetName!CellRange. So, the cell reference for A15 in the March sheet would be =March!A15. Let’s start working on our summary sheet by trying out some 3-D formulas:
For the Annual Amounts in C5:C13 in the Expenses Summary sheet, we don’t need the amount from a single month’s sheets; instead, we need the sum of all the entries in all the monthly sheets. So, we need to sum three-dimensionally through all twelve month sheets. Here’s a helpful hint on the steps you need to follow to add through multiple sheets: Skill RefresherTo SUM across sheets:
Let’s try adding up all the monthly amounts in our Expenses Summary sheet:
If you feel comfortable with these 3-D formulas, you can copy C6 down through C13 to fill in the rest of the formulas. If you’re not quite comfortable yet, keep practicing the above steps to add 3-D formulas to cells C7:C13. When you’re done, your Expenses Summary sheet should match Figure 6.7. While our 3-D formulas are complete in the Expenses Summary sheet, our summary feels like it is lacking something. Let’s add a visual representation of our summary numbers to the sheet.
Skill Refresher3-D References in Formulas To reference a cell in another sheet, use the formula syntax =SheetName!CellAddress. To enter a 3-D reference:
Key Takeaways
Attribution“6.2 Formulas with 3-D References” by Diane Shingledecker, Portland Community College is licensed under CC BY 4.0 What is a 3D reference formula in Excel?A 3D formula is a formula that refers to the same cell (or range of cells) on multiple worksheets. The 3D formula "=SUM(Sheet1:Sheet4! A2)" can be used to add up the numbers in cell "A2" on 4 different worksheets. If you copy or insert a new worksheet after Sheet1 the reference will automatically include it.
How would you use 3D references to add values across worksheets?If you want to analyze data in the same cell or range of cells on multiple worksheets within a workbook, use a mixed 3-D reference. For example, =SUM(Sheet3:Sheet6! A1:A10) returns the sum for all the values contained in the range of cells A1 through A10 on all the worksheets between and including Sheet 3 and Sheet 6.
|