Array formula to SUM rounded values

I format many of my reports to zero decimal places. This means that in some cases the SUM function will show a different total than the displayed numbers indicated. If I format two numbers 5.5 and 7.5 with zero decimal places, they will display as 6 and 8 respectively. When summed that result is 13. Can I get the SUM function to SUM the displayed values, in this case 14?

By Neale Blackwood

The solution is your problem is an array formula. Many of the limitations of Excel’s functions can be overcome by using an array formula. The down side of using array formulae is that they can slow down calculation time.

There are two types of array formulae. Single cell and multi-cell arrays. We will use a single cell array formula to solve the problem above. When you enter, or edit, an array formula you must press the Ctrl, Shift and Enter keys all at once to accept the array formula. This is commonly written as CSE. Excel will then insert brackets { } around the formula.

Array formulae do not conform to the normal conventions of formulae. If you do not use the CSE keys you will usually get an error message or an error displayed. Single cell array formulae normally work on ranges of cells in a way that normal formulae cannot or would require many formulae to achieve the same result.
Assuming A1 contains 5.5 and A2 contains 7.5. An array formula in cell A3 that will solve the above problem is: =SUM(ROUND(A1:A2,0).
You must hold the Ctrl and Shift key and then press the Enter key after typing the formula to insert the array formula. It will display as:

{=SUM(ROUND(A1:A2,0))}

Basically this formula allows you to ROUND a range of cells simultaneously and then SUM them. If you enter the above formula without CSE then a #VALUE error is displayed. To correct it, just select cell A3 and press F2 and then use CSE. A single cell array formula can be copied like any other formula.
If you are interested in learning more about array formulae try a Google search for Excel array formula.

Alternative Solution

Another (simpler) solution is to use SUMPRODUCT(ROUND(A1:A2,0))
This gives the same result and removes the need for array formulas since the SUMPRODUCT formula is essentially an array formula.

(Thanks Steve Zabiela!)