worksheet sum function

[Steve]

I found this the other day. See if this helps.

Use tools>options>calculations and precision as displayed
or use the round function. You probably suffer from
underlying values being not the same as displayed

Regards

Steve

 

[Jerry W. Lewis]

Specifically, if some numbers that are being summed are calculated
rather than entered, they have more figures than you displayed (such as
1/3). Formatting the display does not impact the value in the cell,
therefore is you only want a given number of decimal places (such as 2
for currency) then you have three options

Explicitly round the results
=ROUND(formula,2)
in place of each formula used in the sum

Explicitly round the inputs to the SUM() function
=SUMPRODUCT(ROUND(range,2))
or
=SUM(ROUND(range,2))
The latter must be array entered (Ctrl-Shift-Enter)

Set "Precision as Displayed" per Steve's direction. This will impact
all calculations, not just the sum, so be careful that it does not have
unintended consequences.

Jerry