What does the following MS Excel formula do? =SUM(Sheet1:Sheet4!D18)
2023
What does the following MS Excel formula do? =SUM(Sheet1:Sheet4!D18)
Answer: A. It adds data from all D18 cells in Sheet1, Sheet2, Sheet3 and Sheet4 — ConceptA 3-D reference addresses the same cell or range across a contiguous span of worksheets. Its general form is FirstSheet:LastSheet!CellOrRange; both…
- A.
It adds data from all D18 cells in Sheet1, Sheet2, Sheet3 and Sheet4
- B.
It adds data from cell D18 of Sheet1 and cell D18 of Sheet4
- C.
It adds all data in the range A1:D18 in Sheet1, Sheet2, Sheet3 and Sheet4
- D.
It adds data from cell A1 of Sheet1 and cell D18 of Sheet4
Attempted by 2 students.
Show answer & explanation
Correct answer: A
Concept
A 3-D reference addresses the same cell or range across a contiguous span of worksheets.
Its general form is FirstSheet:LastSheet!CellOrRange; both endpoint sheets and every sheet between them are included.
An aggregate function such as SUM then combines all values returned by that 3-D reference.
Application
Sheet1:Sheet4denotes the continuous worksheet span from Sheet1 through Sheet4, inclusive.The exclamation mark separates the worksheet span from the cell reference, and
D18selects that same cell on every sheet in the span.SUMadds the four referenced values:Sheet1!D18,Sheet2!D18,Sheet3!D18, andSheet4!D18.
Cross-check
A two-sheet-only sum would use separate references such as SUM(Sheet1!D18,Sheet4!D18). A rectangular range on every sheet would include a cell-range colon after the exclamation mark, such as Sheet1:Sheet4!A1:D18.
Result
Therefore, the formula adds the values in cell D18 of Sheet1, Sheet2, Sheet3, and Sheet4.