Why your Excel SUM is not adding up correctly

Off by a cent: the cells hold hidden decimals

The sheet is an office supplies order with a 12% discount in G1. Each line total is quantity times price times (1 - discount), and column D is formatted to 2 decimals. Add the five product lines as they appear on screen, 65.97 + 33.26 + 73.04 + 12.28 + 43.96, and you get 228.51. SUM says 228.52.

Both are "right". Number formatting only changes what a cell shows. The desk lamp line really holds 65.9736 and the cable ties 12.276. SUM adds those full values, gets 228.5184, and the 2-decimal format shows that as 228.52. A calculator, or a person checking the printout, adds the rounded numbers and gets a cent less.

To see what a cell really holds, select it and look at the formula bar, or click Home > Increase Decimal a few times.

Fix it: round where the value is calculated

Money should be rounded to cents at the step where a cent amount is created, the line total here, not only formatted. Change the line formula to =ROUND(B2*C2*(1-$G$1),2) and fill it down. Now every cell holds exactly what it shows, and SUM returns 228.51, the same as an invoice or a calculator.

If you cannot change the line formulas (a linked sheet, someone else's file), round inside the total instead. SUMPRODUCT rounds each line first and then adds. =SUM(ROUND(D2:D6,2)) does the same in Excel 365 and 2021, but in Excel 2019 and earlier it needs Ctrl+Shift+Enter, so SUMPRODUCT is the safer choice.

Do not just wrap the total in ROUND. =ROUND(SUM(D2:D6),2) still gives 228.52, because it rounds the sum of the unrounded lines.

The total does not equal the number you type

Hidden decimals also break checks. =SUM(D2:D6)=228.52 returns FALSE even though the cell shows 228.52, because the stored value is 228.5184. The same thing makes IF tests, conditional formatting and lookups against a total fail for no visible reason.

Compare rounded values instead. =ROUND(SUM(D2:D6),2)=228.52 returns TRUE.

A second, smaller source of this is floating-point arithmetic. Excel stores numbers in binary, so some decimals cannot be stored exactly. =0.5-0.4-0.1 does not return 0 but a tiny negative number, about -2.78E-17. In 2-decimal cells you never see it, but it can make a check for a difference of exactly 0 fail. Rounding the result fixes it the same way.

Off by a larger amount: numbers stored as text

Now total the whole order including shipping and the return credit. SUM(D2:D8) returns 247.0184, about 247.02. It should be 15 less: the return credit in D8 was pasted from a refund email as the text "-15.00". SUM skips text in a range without any warning, so the credit simply vanishes.

Shipping in D7 is fine, even though its price in C7 is also text. Arithmetic (+, -, *, /) converts text that looks like a number, so =B7*C7 returns the number 18.5. SUM does not convert text inside a range. That is why =D7+D8 returns 3.5 while SUM leaves D8 out.

A quick test: =COUNT(D2:D8) counts numbers and returns 6, while =COUNTA(D2:D8) counts filled cells and returns 7. When those two differ, a cell is text. Convert it with the steps in the numbers stored as text guide, then the total drops to 232.02 (232.01 once the lines are rounded to cents).

Other reasons a total looks wrong

Hidden and filtered rows. SUM adds every row in its range, including rows you hid or filtered out. If you expect the total of what you can see, use =SUBTOTAL(109,D2:D8).

A subtotal inside the range. If the column already has subtotal rows, a SUM over the whole column counts those amounts twice. Use SUBTOTAL for the subtotals and the grand total, because SUBTOTAL skips other SUBTOTAL results in its range.

The range stops short. A total typed or AutoSummed before new rows were added may not include them. Click the total and check the coloured outline on the sheet. Turning the list into a table (Ctrl+T) and using a total row keeps the range growing with the data.

The total does not change at all. If editing a number leaves the total as it was, the workbook is on manual calculation: Formulas > Calculation Options > Automatic.

Set precision as displayed: only if you understand it

Excel has a setting that makes every cell hold exactly what it displays. It fixes the cent problem in one go, and it also permanently throws away the hidden decimals in every constant in the workbook. You cannot get them back by turning the setting off.

It also applies to every sheet in the file, so a rate typed as 0.0375 in a 2-decimal cell becomes 0.04. Use ROUND in your formulas instead. If you do turn it on, save a copy of the file first.

1. Go to File > Options > Advanced.
2. Scroll to "When calculating this workbook".
3. Tick "Set precision as displayed" and click OK on the warning that data will permanently lose accuracy.

Which fix to pick

Off by a cent or two: round each money value where it is calculated with ROUND(...,2). If you cannot touch those formulas, total with SUMPRODUCT(ROUND(range,2)). Off by a round amount that matches one or more cells: check COUNT against COUNTA and convert the text numbers. Total includes rows you cannot see: use SUBTOTAL(109,...). Total does not move when you edit: switch calculation back to Automatic.