Excel track · Exercise 6 of 86 Paid

SUM across several blocks

Add several separate blocks, or one block with gaps, in a single SUM call.

Intermediate

Split quarters and a three-team block with gaps

Read-only data · screen 1 of 2 · answer in B4

In B4, total both quarters, which sit in two blocks with an empty column between them.

	A	B	C	D
1	Team	Q1		Q2
2	North	320		300
3	South	250		260
4	Both quarters

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

In B5, total the whole block, skipping the two text cells and the empty one.

	A	B	C	D
1	Team	Jan	Feb	Mar
2	North	320	180	300
3	South	250	n/a	260
4	West	400		150
5	Block total

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

Reference answer

What it does

SUM takes as many arguments as you need, and each one can be its own block. Quarters sitting in columns B and D go in one call as =SUM(B2:B3,D2:D3), with the comma doing the work. Drop the comma and the two blocks make a single broken reference that Excel refuses to read.

When the blocks form a rectangle, one range covers the lot. A formula of =SUM(B2:D4) adds every number inside and skips the text in the middle and the empty corner. That is why a block total survives cells that a chain of plus signs does not.

Check the edges of the block. A rectangle is only too big when the extra cells hold numbers, and a total row always does.

Syntax

=SUM(range1, range2, ...) across separate blocks, or =SUM(B2:D4) across one rectangle

Worked example

On Split quarters and a three-team block with gaps, =SUM(B2:B3,D2:D3) returns 1130.

=SUM(B2:B3,D2:D3)

Common errors

Error codes for SUM with multiple block arguments and a rectangular range with text in it
Error	What it means and how to fix it
`#VALUE!`	A cell in a hand-written chain holds text. SUM over a whole range would have stepped over it.
`#REF!`	A block names a range that no longer exists after rows or columns were deleted.
`Too wide`	A rectangle that swallows its own total row adds the subtotal to the detail. Check the last row of the range.

At work

A board pack whose two halves sit apart on the sheet, and an imported block with gaps in it.