Ravindra BagaleCourses & study guides मराठी Track your progress

23. Formulas Across Sheets and Workbooks

23.4 3D References: One Formula Across Many Sheets

By Last updated:

Example first. Jan, Feb and Mar have exactly the same layout. On a Q1 sheet, =SUM(Jan:Mar!B2) adds B2 of all three sheets: Pune Q1 = ₹12,65,000. One formula, three sheets.

3D reference — in 30 seconds

What: A 3D reference covers the same cell (or range) on every sheet between two sheet tabs: Jan:Mar!B2 means B2 on Jan, on Feb, on Mar and on any sheet placed between them.

Why: Monthly sheets with the same layout are very common. A 3D SUM gives the quarter or the year without a long =Jan!B2+Feb!B2+Mar!B2 chain.

Tiny example: =SUM(Jan:Mar!B2) = 4,12,000 + 3,98,000 + 4,55,000 = ₹12,65,000.

First look at the data (before → after):

3D reference: before and after tables, fictional Blinkit Maharashtra data

Expected result — Sheet Q1

A: Outlet B: Q1 sales (₹) C: Monthly average (₹)
Pune 12,65,000 4,21,667
Nashik 8,41,000 2,80,333
Nagpur 7,14,000 2,38,000
Kolhapur 5,66,000 1,88,667
Total 33,86,000 —
Before table and formula as text

Before — Sheet Jan (A1:B5)

A: Outlet B: Sales (₹)
Pune 4,12,000
Nashik 2,68,000
Nagpur 2,31,000
Kolhapur 1,89,000

Before — Sheet Feb (A1:B5)

A: Outlet B: Sales (₹)
Pune 3,98,000
Nashik 2,81,000
Nagpur 2,44,000
Kolhapur 1,76,000

Before — Sheet Mar (A1:B5)

A: Outlet B: Sales (₹)
Pune 4,55,000
Nashik 2,92,000
Nagpur 2,39,000
Kolhapur 2,01,000

Formula / click path

Q1!B2: =SUM(Jan:Mar!B2)   → fill down to B5
Q1!C2: =AVERAGE(Jan:Mar!B2)
Q1!B6: =SUM(Jan:Mar!B2:B5)

Do it step by step:

  1. Make sure Jan, Feb and Mar have the outlets in the same cells (A2:A5) and sales in B2:B5.
  2. Put the three sheet tabs next to each other: Jan, Feb, Mar.
  3. On the Q1 sheet, click B2 and type =SUM(.
  4. Click the Jan tab, hold Shift and click the Mar tab, then click B2. Press Enter. Excel writes =SUM(Jan:Mar!B2).
  5. Fill down to B5.
  6. In B6 type =SUM(Jan:Mar!B2:B5) for the grand total.

What 3D references can and cannot do

Works Does not work
SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV and similar SUMIF, SUMIFS, COUNTIF, COUNTIFS, XLOOKUP, VLOOKUP (they do not accept 3D ranges)
Any sheet moved between the first and last tab is included A sheet moved outside the two tabs is dropped silently
Same cell on every sheet Sheets with a different layout give wrong totals without any error

The Start/End trick. Insert two empty sheets named Start and End and keep every month between them. Write =SUM(Start:End!B2). Adding April is now just "insert a sheet before End". Deleting Jan (the first sheet of Jan:Mar) changes the formula to Feb:Mar; with Start and End there is no surprise.

Practice task (try it before you open the answer):

  1. Build the Q1 sheet with 3D SUM and AVERAGE.
  2. Insert a copy of Mar named Apr between Feb and Mar and read Pune Q1 again.
  3. Drag Apr to the right of Mar and read it again.
Answer

With Apr (a copy of Mar) between Feb and Mar, Pune becomes ₹17,20,000. After moving Apr outside Jan:Mar it goes back to ₹12,65,000. Nothing warns you, which is why the Start/End sheets help.

Ravindra Bagale's Tip

Colour the tabs of sheets that are inside a 3D range the same colour. Anyone who drags a green tab outside the green group can see they just changed a total.