Why don't my rounded numbers add up to the total in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(ROUND(B2:B6,2))The cells show rounded values but store more decimals, so add the rounded values: =SUMPRODUCT(ROUND(B2:B6,2)).
- Result on the example
- $73.68 in
E1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.
How it works
Each amount has a fraction of a cent the currency format hides: 12.344 shows as $12.34. Plain SUM adds the hidden fractions too and shows $73.70, two cents more than the lines you can see add up to. ROUND brings each value to the cent before adding, so the total is $73.68, the amount you'll actually pay out line by line.
=SUMPRODUCT(ROUND(B2:B6,2))Result73.68
B2:B65 rows × 1 col: $12.34, $7.12, $30.00, $8.67, …A range: 5 cells in one column.
ROUND(B2:B6,2){5 rows × 1 col} 12.34, 7.12, 30, 8.67, …ROUND: Rounds to a number of digits; negative digits round left of the decimal.- number
B2:B65 rows × 1 col: $12.34, $7.12, $30.00, $8.67, … - num_digits
2
- number
SUMPRODUCT(ROUND(B2:B6,2))73.68SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
ROUND(B2:B6,2){5 rows × 1 col} 12.34, 7.12, 30, 8.67, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(ROUND(B2:B6,2)). We ran it in Google Sheets mode on the same data and it returns $73.68.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(ROUND(B2:B6,2))Excel only | $73.68 | Excel 365 handles the array directly; older versions need Ctrl+Shift+Enter and Google Sheets needs ARRAYFORMULA. |
| =SUM(B2:B6) | $73.70Different result | The unrounded total. Includes the hidden fractions of a cent: 73.70. |
Common mistakes
- The cleanest fix is upstream: round each line where it is calculated (
=ROUND(miles*rate,2)), then a plain SUM always matches. - Excel's "Set precision as displayed" option rounds every stored value permanently. It can't be undone, so avoid it on shared workbooks.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Why don't my rounded numbers add up to the total
E1, total the amounts as they are shown, rounded to the cent, so the total matches the visible lines.Questions
Which total is right?
Whichever matches how the amounts are paid. If each line is paid separately, the total of rounded lines is what leaves the bank.
Why do percentages add up to 99% or 101%?
The same reason: each share is rounded separately. Adjust the largest share so the shown values add to 100%, or note that totals may not add due to rounding.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.