Why Your Excel Total Is Wrong (and How to Fix It)

When Excel SUM is not working, the formula usually isn't broken: it's adding exactly what you told it to, which isn't what you meant. Excel warns you when a formula fails, but not when it gives the wrong answer. This guide walks through the causes in the order to check them, using one small expense list with three hidden bugs.

Why is my Excel SUM not working?#

Most wrong totals come from three causes: a range that misses rows, numbers stored as text, and a total that counts itself. A few rarer causes are covered further down.

Here is the expense list. The total in B8 shows 2,985, but the real total is 3,256.

Nothing on the screen looks wrong. That's why you need a checklist.

Cause 1: the range misses a row#

Click the total and read its formula in the formula bar. Here it's =SUM(B2:B6), but the data runs to row 7. Someone added Software later, and the range never grew to include it.

Fix: edit the range so it covers every row:

=SUM(B2:B7)

The total becomes 3,114. Still not 3,256, because a second bug is hiding.

To stop this happening again, insert new rows inside the range (above the last row) rather than below it, and Excel expands the range for you. Or format the list as a Table, whose totals grow with the data. When you select the total cell, Excel also draws a coloured border around the range it uses, which makes a short range easy to spot. AutoSum guesses ranges for you, but it stops at gaps, so always check its guess.

Cause 2: numbers stored as text#

Look at B5. Its 142 sits on the left of the cell, while real numbers sit on the right. It is a number stored as text: Excel treats it as a word. This often happens with data copied from websites, PDFs or other systems, or typed with an apostrophe in front ('142). Excel may show a small green triangle in the corner.

SUM skips text without any warning. Prove it with COUNT, which counts only real numbers:

Six expenses, five numbers: one is text. The third formula is a check you can reuse on any list. A clean column returns 0. COUNT, COUNTA and COUNTBLANK explains each function.

Fixes, from quickest to most flexible:

• Retype the value. Select B5, type 142, press Enter.
• Convert to Number. Select the cells, click the warning icon next to them and choose Convert to Number.
• Use VALUE. In a helper column, =VALUE(B5) turns the text into the number 142.
• Clean the data. Spaces or invisible characters from the web can also make numbers text. TRIM and CLEAN remove them; see clean messy data in Excel.

Once B5 is a real number, the total updates to 3,256.

One odd detail: =B2+B5 gives 2,542 even while B5 is text, because the + sign converts number-like text. SUM doesn't. That's why a total written with + can disagree with SUM on the same cells.

Cause 3: the total includes itself#

Now say a colleague changes B8 to =SUM(B2:B8). The range includes B8, the total itself. To work out the total, Excel would first need the total. This is a circular reference. Excel shows a warning, and the cell usually shows 0.

Fix: end the range one row above the total:

=SUM(B2:B7)

If you can't find the problem cell, go to Formulas, open the arrow next to Error Checking and point to Circular References. Excel lists the cells involved.

Other reasons your total looks wrong#

If the range, the text check and the circular reference are all fine, check these:

A quick checklist before you trust any total#

Run these four checks on any total before you send a report or finish a test:

1. Read the range. Does it start at the first data row and end at the last?
2. Count the numbers. Does =COUNT(range) match the number of rows you expect?
3. Look at the alignment. Are any numbers sitting on the left?
4. Sanity-check the size. Is the total roughly what you'd expect? A quick look at the largest and smallest values with MAX and MIN helps.

If you're still learning how SUM handles ranges, commas and blank cells, the beginner's guide to the SUM function and the SUM function reference cover the basics.

Practise it#

Spotting a wrong total is a skill you build by fixing real ones. Practise debugging SUM in a live spreadsheet: you'll fix the short range, find and convert the text number with COUNT, untangle a circular reference and build the reusable text check. Then try the exercise on this page.