How to Combine the ROUND and SUM Functions in Excel

Illustration of a person using Excel to combine Round and Sum functions

Lifewire / Theresa Chiechi 

Combining the operations of two or more functions, such as ROUND and SUM, in a single formula within Excel is referred to as a nesting function. Nesting is accomplished by having one function act as an argument for the second function. Follow this tutorial and learn how to properly nest functions and combine operations in Microsoft Excel.

Information in this article applies to Excel 2019, 2016, 2013, 2010, 2007; Excel for Mac, Excel for Office 365, Excel Online, Excel for Android, Excel for iPhone, and Excel for iPad.

General Rules For Combining Functions in Excel

When evaluating nested functions, Excel always executes the deepest or innermost function first and then works its way outward.

Screenshot of Microsoft Excel with Combined Functions

Depending on the order of the two functions when combined, the following applies:

  • Rows or columns of data are summed and then rounded to a set number of decimal places all within a single worksheet cell (see row 6 above).
  • Values are rounded and then summed (see row 7 above).
  • Values are rounded and then summed, all in a single cell using a SUM/ROUND nested array formula (see row 8 above).

Since Excel 2007, the number of levels of functions that can be nested inside each other is 64. Prior to this version, only seven levels of nesting were permitted.

Combine the ROUND and SUM Functions

Although it is possible to enter the complete formula manually, you may find it easier to use the Function Arguments dialog box to enter the formula and arguments.

=ROUND(SUM(A2:A4),2)

The dialog box simplifies entering the function's arguments one at a time without having to worry about the function's syntax such as the parenthesis surrounding the arguments and the commas that act as separators between the arguments.

Screenshot of Microsoft Excel combining SUM and ROUND functions.

Even though the SUM function has its own dialog box, it cannot be used when the function is nested inside another function. Excel doesn't allow a second dialog box to be opened when entering a formula.

To follow this tutorial to use the ROUND and SUM functions together, start by entering the data in rows 1, 2, 3, 4, and 5. Then, follow these steps:

  1. Select cell B6 to make it the active cell.

  2. Select the Formulas tab of the ribbon.

  3. Select Math & Trig to open the function drop-down list.

  4. Select ROUND in the list to open the Function Arguments dialog box. On a Mac, the Formula Builder opens.

  5. Place the cursor in the Number text box.

  6. Type SUM (A2:A4) to enter the SUM function as the Number argument of the ROUND function.

    A screenshot showing how to combine the ROUND and SUM functions in Excel
  7. Place the cursor in the Num_digits text box.

  8. Type a 2 to round the answer to the SUM function to 2 decimal places.

  9. Select OK to complete the formula and return to the worksheet. Except in Excel for Mac, where you select Done instead.

  10. The answer 764.87 appears in cell B6 since the sum of the data in cells D1 to D3 (764.8653) is rounded to 2 decimal places.

  11. Select cell C3 to display the nested function in the formula bar above the worksheet.

Use an Excel Array / CSE Formula

An array formula, such as the one in cell B8, allows for multiple calculations to take place in a single worksheet cell. An array formula is readily recognized by the braces or curly brackets { } that surround the formula.

Screenshot of Microsoft Excel with function arrays.

These braces are not typed in, however, but are entered by pressing the Shift+Ctrl+Enter keys on the keyboard. Because of the keys used to create them, array formulas are sometimes referred to as CSE formulas.

Array formulas are normally entered without the aid of a function's dialog box. To enter the SUM/ROUND array formula in cell B8, use this formula:

{=ROUND(SUM(A2:A4),2)}
  1. Select cell B8 to make it the active cell.

  2. Type the formula:

    {=ROUND(SUM(A2:A4),2)}
  3. Press and hold the Shift+Ctrl keys.

  4. Press the Enter key.

  5. Release the Shift+Control keys.

  6. The value 764.87 appears in cell B8.

  7. Select cell B8 to display the array formula in the formula bar.

Use Excel's ROUNDUP and ROUNDDOWN Functions

Excel has two other rounding functions that are very similar to the ROUND function. They are the ROUNDUP and ROUNDDOWN functions. These functions are used when you want values to be rounded in a specific direction, rather than relying on Excel's rounding rules.

Screenshot of Microsoft Excel and ROUNDUP function.

Since the arguments for both of these functions are the same as those of the ROUND function, either can easily be substituted into the nested formula shown in row 6.

The form of the ROUNDUP/SUM formula is:

=ROUNDUP(SUM(A2:A4),2)

The form of the ROUNDDOWN/SUM formula is:

=ROUNDDOWN(SUM(A2:A4),2)