Excel Formulas Across Sheets and 3D References (SUM Jan:Mar)

Q: How do I refer to a sheet name with spaces?

Wrap it in single quotes: ='Sambhaji Nagar'!B2 .

By Ravindra Bagale, Excel & Data Analytics trainer LinkedIn Published: 6 October 2026 Last updated: 6 October 2026

Amir keeps one sheet per month: Jan, Feb and Mar. Each sheet has the same layout, with Pune sales in cell B2. For the quarter report he wants Pune's total of the three months. Instead of =Jan!B2+Feb!B2+Mar!B2, he types =SUM(Jan:Mar!B2) and gets ₹3,75,000.

A sheet reference points at a cell on another sheet, like =Jan!B2. A 3D reference points at the same cell on a group of side-by-side sheets, like Jan:Mar!B2, so one SUM adds them all.

Download the practice file (formulas-across-sheets-3d-references.xlsx)

Excel file, 8 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.

Quick answer

On the Summary sheet, click the cell for the answer.
Type =SUM(
Click the Jan sheet tab, hold Shift and click the Mar tab.
Click cell B2 and press Enter.
The formula bar shows =SUM(Jan:Mar!B2). Copy it down for the other cities.

=Jan!B2
=SUM(Jan:Mar!B2)

Step 1: refer to one other sheet

Click the cell where you want the answer.
Type =
Click the Jan sheet tab and click B2.
Press Enter. The formula is =Jan!B2 and shows 120000.

If a sheet name has a space, Excel adds single quotes: ='Sambhaji Nagar'!B2. Type them yourself if you write the formula by hand.

Step 2: total the same cell across months

City	Jan	Feb	Mar	=SUM(Jan:Mar!B…)
Pune	1,20,000	1,25,000	1,30,000	3,75,000
Solapur	65,000	62,000	68,000	1,95,000
Nashik	90,000	94,000	97,000	2,81,000
Sambhaji Nagar	70,000	72,000	75,000	2,17,000
Kolhapur	60,000	58,000	63,000	1,81,000
Nagpur	85,000	88,000	91,000	2,64,000

On Summary, B2 holds =SUM(Jan:Mar!B2). Copy it down to B7; the row number changes for each city. Grand total in B8: =SUM(Jan:Mar!B8) gives 15,13,000.

Rules that keep 3D formulas correct

Every month sheet must have the same layout. Pune must be in B2 on every sheet.
The formula includes every sheet placed between Jan and Mar. If you drag a new sheet between them, it is added to the total.
To add April, place the Apr sheet inside the group (for example between Feb and Mar) or change the formula to Jan:Apr.
3D references work with SUM, AVERAGE, MAX, MIN, COUNT and COUNTA. They do not work with SUMIFS, COUNTIFS or XLOOKUP.

Bookend trick

Insert two blank sheets named Start and End around your month sheets and use =SUM(Start:End!B2). Any month you drag between the bookends is counted.

When to use something else

Months with different layouts: use Data > Consolidate, or Power Query to append the sheets into one table.
Lots of months: keep one long table with a Month column and use SUMIFS. One table is easier to filter and pivot than twelve sheets.

Ravindra Bagale's Tip

Before you trust a 3D total, check the sheet order at the bottom. One stray sheet dragged between Jan and Mar silently changes every quarter total.

Practice task

Open the practice file. Check the Jan, Feb and Mar sheets have Pune in B2.
On Summary, check that B2 holds =SUM(Jan:Mar!B2) and shows 375000.
In C2 type =AVERAGE(Jan:Mar!B2). Expect 125000.
Copy the Mar sheet (right-click the tab > Move or Copy > Create a copy) and drag the copy between Feb and Mar.
Watch the Summary totals change, then delete the copy.

Learn it properly

Course lessons:

Frequently asked questions

What is a 3D reference in Excel?

A reference to the same cell or range on several adjacent sheets, written like Jan:Mar!B2.

Why does my 3D total include an extra sheet?

A sheet sits between the first and last sheet names. Move it outside the group.

Can I use SUMIFS with a 3D reference?

No. SUMIFS does not accept 3D references. Put the months in one table with a Month column, or sum each sheet separately.

How do I refer to a sheet name with spaces?

Wrap it in single quotes: ='Sambhaji Nagar'!B2.