Learn a useful worksheet habit

Excel SUM: include the whole invoice range

A total can look right while its formula leaves out an invoice row. Predict the result, check the full range, then ask what happens when an input changes.

Before you start

This guide accompanies F05: SUM and range boundaries in SheetFluency’s Formula foundations course. Follow F01: Formula entry and linked inputs → F02: Addition and subtraction → F05: SUM and range boundaries. F05 requires F02; F03 and F04 are not prerequisites for F05. The course link below opens Formula foundations and does not bypass lesson prerequisites.

The first example uses synthetic amounts from the course’s base worked example. Guided and transfer tasks can use different numbers, addresses and row positions. Use the task you are shown; 350 is not a universal answer.

In a course task, enter formulas only in its assigned output cell and keep supplied inputs unchanged. To recreate or change these examples yourself, use a separate, disposable blank practice workbook. Do not overwrite restored work.

1. Predict the total

You are checking five invoice rows. One contains a credit note, one is zero, and one is still blank.

Base worked example: invoice amounts in B2:B6
Amount cell	Invoice label in column A	Amount
B2	INV-1	125
B3	INV-2	240
B4	INV-3	0
B5	INV-4	-15
B6	INV-5	blank

Should the credit note increase or reduce the total?
What total do you expect today?
Should your formula include B6 even though it is blank?

Check your prediction

125 + 240 + 0 − 15 = 350. The credit reduces the total. B6 belongs to the invoice range even though an amount has not arrived yet.

2. Practice with the whole range

In this example’s total cell B8, enter =SUM(B2:B6). For this exact example, the result is 350. In a course task, use the actual addresses and amounts shown there.

Read it aloud: “Sum every cell from B2 through B6, including both ends.” The colon defines a continuous range. Zero adds nothing, the credit subtracts 15, and the genuinely blank cell contributes no number today.

Check both the result and the range. =SUM(B2:B5) gives the same total today but would miss a later amount in B6.

3. Explain the relationship

Why is =SUM(B2:B5) incomplete even when it displays 350?
How is entering 350 different from entering =SUM(B2:B6)?
Why should you not turn the credit note into positive 15?

Check your explanation

The full range keeps all five invoice rows linked. A typed answer stays fixed when inputs change. The credit already has its sign; changing it would change the meaning of the supplied data.

A linked addition formula such as =B2+B3+B4+B5+B6 can describe the same relationship. F05 also asks you to demonstrate SUM with a multi-cell range. A correct business result and demonstrating the requested technique are related, but not identical.

4. Transfer: predict a changed input

Imagine that INV-5 arrives with an amount of 60 in B6. What should the total become? Which of these entries would update correctly?

=SUM(B2:B6)
=SUM(B2:B5)
350

Check the changed-input answer

The total becomes 410. Only the first choice includes the new B6 input. The second and third remain 350 in this example.

This is a reasoning prompt, not an instruction to edit a graded task’s supplied inputs. If practicing in a separate disposable workbook, change B6 there and inspect both the formula and result.

Try a fresh example before checking the answer

A new invoice range occupies D2:D6. Write a SUM formula that includes all five rows. Predict its result. Then predict what happens if D4 receives 25.

Separate practice example: invoice amounts in D2:D6
Cell	Amount
D2	90
D3	-10
D4	blank
D5	0
D6	45

Check the fresh-example answer

=SUM(D2:D6) returns 125; after D4 becomes 25, it returns 150. The correct range changed because the data moved to column D. Do not reuse B2:B6 just because it worked in the first example.

When something does not match

Checks for these examples
What you notice	Check this
You get 365 in the first example	Did your range stop before the negative credit? Check both endpoints.
You get 380 in the first example	Did you change -15 to +15? Keep the supplied sign.
You get 350, but the new invoice is missing from the result	Check whether B6 is inside the range, and whether the cell stores a formula rather than a fixed answer.
Your formula uses B2:B6 in the new example	The new amounts are in D2:D6. References must match the current worksheet.
You used linked addition and got the right total	Good relationship; now show the requested SUM-range technique without changing the inputs.

Takeaway: include the complete intended range, preserve the signs, and check that the result follows changed inputs.

Practice in Formula foundations

This guide is practice, not a scored course attempt. Worked or revealed answers are not independent evidence of understanding. For an independent check, solve a fresh, unrevealed task in the course and follow its available progression.

Open Formula foundations

Examples and explanations are original SheetFluency practice material, aligned with Formula foundations F05. The D2:D6 exercise is a separate companion example, not a generated course task.