Level 1 · Three channel reports, one quarter total

Consolidating means turning several reports into one. The simplest case is when every report uses the same template: same categories, same order, same months across the top. Then cell B3 means “Decor in January” in every block, and the total for Decor in January is just the three B3s added together. Excel calls this consolidating by position.

The three blocks

Anthony’s sheet holds Q1 2025 revenue from completed orders, one block per channel:

Block	Rows	Decor, January
Web	header in row 2, data in rows 3–8	B3 = 8,299.05
App	header in row 11, data in rows 12–17	B12 = 8,101.00
Marketplace	header in row 20, data in rows 21–26	B21 = 2,946.55

The blocks are nine rows apart, so the same category and month is always nine and eighteen rows below its Web cell. The empty template in F1:I8 has the same shape.

One formula, filled two ways

G3   =SUM(B3,B12,B21)

Decor in January: 8,299.05 + 8,101.00 + 2,946.55 = 19,346.60. Fill the formula right and every reference moves one column, so H3 reads =SUM(C3,C12,C21), February. Fill it down and they move one row, so G4 reads =SUM(B4,B13,B22), Furniture. Eighteen cells, one formula.

=B3+B12+B21 gives the same numbers here. The difference shows up on bad data: + returns #VALUE! if one of the cells holds text, while SUM skips the text and adds the rest. That is why Excel’s own Consolidate tool behaves like SUM.

How you would do it across sheets

In a real workbook the blocks would be three sheets named Web, App and Marketplace, each with the template in the same cells. Excel has two tools for that, both doing what your formula does:

• A 3D reference. =SUM(Web:Marketplace!B3) adds B3 on every sheet from Web to Marketplace, in tab order. Fill it like any other formula. A sheet dragged between the first and last tab is included automatically. Google Sheets has no 3D references: write =SUM(Web!B3,App!B3,Marketplace!B3).
• Data → Consolidate. Pick the function (Sum), add each source range with Add, leave “Top row” and “Left column” unticked for position, and click OK. Ticking Create links to source data makes the result update when a source changes; without it you get plain numbers that go stale.

This sheet has one worksheet, so the formula above is the version the checker can see.

Check the total

A consolidated block should add up to the sum of its sources. The Web block’s Q1 total is =SUM(B3:D8), the App and Marketplace blocks likewise, and the three together must equal =SUM(G3:I8). On this sheet both sides come to 417,261.59. One cross-check like this catches a block that was skipped or counted twice.

Common mistakes

• One row off in one block. =SUM(B3,B12,B20) reads the “Jan” header in B20. SUM ignores text, so Decor shows 16,400.05 and nothing looks broken. Filled down, every category then picks up the marketplace number of the row above it: wrong everywhere, with no error in sight.
• Blocks that only look alike. Position works only if every source has the same rows in the same order. If one channel sorts its categories differently, or adds a seventh, the totals mix categories. The next level handles that case.
• Locking references. $B$3 would freeze every cell on Decor in January. Here all three references must move.