How to Combine the ROUND and SUM Functions in Excel

What to Know

  • After entering the tutorial data in rows 1 through 5, select cell B6 to make it active. Go to Formulas and select Math & Trig > ROUND.
  • Place the cursor in the Number text box and enter SUM(A2:A4). Place the cursor in the Num_digits text box and enter a 2. Select OK.
  • The answer of the combined ROUND and SUM functions appears in cell B6.

This article demonstrates how to combine the ROUND and SUM functions in Excel with a tutorial example. It also includes information on using an Excel array CSE formula and using the ROUNDUP and ROUNDDOWN functions. This information applies to Excel 2019, 2016, 2013, 2010, 2007; Excel for Mac, Excel for Microsoft 365, Excel Online, Excel for Android, Excel for iPhone, and Excel for iPad.

Combine the ROUND and SUM Functions

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.

Screenshot of Microsoft Excel combining SUM and ROUND functions.

Begin by entering the data in rows 1, 2, 3, 4, and 5 shown in the image above. 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 B6 to display the nested function in the formula bar above the worksheet.

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.

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.

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)

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.

Illustration of a person using Excel to combine Round and Sum functions
Lifewire / Theresa Chiechi
FAQ
  • Can I also use ROUND on multiplication sums?

    Yes, ROUND (along with ROUNDUP and ROUNDDOWN) will also work with multiplication totals. It's a similar formula, except ignore "SUM" and use "*" to multiply cells. It should look something like this: =ROUNDUP(A2*A4,2). The same approach can also be used for rounding other functions like cell value averages.

  • How do I tell Excel to round to the nearest whole number?

    Rounding to whole numbers instead of decimals is simply a matter of using "0" in the decimal place spot for the SUM formula. It should look something like =ROUND(SUM(A2:A4),0).

  • How do I prevent Excel from automatically rounding up numbers for me?

    Excel may automatically round a cell's value if the cell itself is too narrow to show the full number, or it could be caused by your worksheet's format settings. To make Excel display the full number (without manually expanding each cell), select the cell > Home tab > Increase Decimal. Continue selecting Increase Decimal until it displays as much of the cell's number as you want.

Was this page helpful?