# Why don't my rounded numbers add up to the total in Excel

The cells show rounded values but store more decimals, so add the rounded values: `=SUMPRODUCT(ROUND(B2:B6,2))`.

**Excel:** `=SUMPRODUCT(ROUND(B2:B6,2))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Trip | Mileage owed |  | Total to pay |
| Client visit | 12.344 |  |  |
| Supplier site | 7.124 |  |  |
| Conference | 30.004 |  |  |
| Training | 8.674 |  |  |
| Warehouse | 15.554 |  |  |

Formula in E1: `=SUMPRODUCT(ROUND(B2:B6,2))`

Result: $73.68

## 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.

## Other ways

- `=SUM(ROUND(B2:B6,2))`: Excel 365 handles the array directly; older versions need Ctrl+Shift+Enter and Google Sheets needs ARRAYFORMULA.
- `=SUM(B2:B6)`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/rounded-numbers-dont-add-up