To add values and round the final total in one step, use =ROUND(SUM(B2:B5),2). This example assumes the numbers are in B2:B5; if they add up to 10.376, the formula returns 10.38. It combines Excel’s SUM function, which adds the values, with ROUND, which sets the number of decimal places. You can change the range and the rounding precision to suit your sheet.
This approach is useful for totals that need a consistent number of decimal places, such as quantities, measurements, or prices. The key choice is whether you want to round the final total or round each item before adding it: those methods can produce different results.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows. The same formula works in current Excel for Mac and Excel for the web.
- A worksheet with the numbers you want to add, ideally in one continuous range such as B2:B5.
- A decision about the number of decimal places to keep. Use 2 for two decimal places, 0 for a whole number, or a negative number to round to tens, hundreds, or another place to the left of the decimal point.
Step-by-Step
-
Arrange the values and identify the range. Put the values you want to total in a column or row. For example, enter 2.125 in B2, 3.257 in B3, 1.004 in B4, and 3.990 in B5. In this example, the range to add is B2:B5. A colon between the first and last cell means Excel includes every cell between them.
Check that the cells contain numbers rather than numbers stored as text. A number usually aligns to the right by default, although cell formatting can change alignment. If a value has a warning indicator or does not behave like a number in calculations, see Troubleshooting below.
-
Select the cell where the rounded total should appear. Click an empty cell, such as B6. Keeping the answer outside the range being added prevents a circular reference. If you enter a formula in B5 while also adding B2:B5, Excel will try to include the formula cell in its own calculation.
-
Enter ROUND around SUM. With the example values in B2:B5 and the result intended for B6, type
=ROUND(SUM(B2:B5),2)in B6, then press Enter. SUM adds 2.125 + 3.257 + 1.004 + 3.990, which equals 10.376. ROUND then rounds that total to two decimal places, so the result is 10.38.The structure is important: SUM is inside ROUND, so Excel adds the complete range first and rounds the combined answer afterward. The 2 after the comma tells ROUND to keep two decimal places. In Excel for Microsoft 365 on Windows, you can type the formula directly in the cell or in the formula bar. On Mac and in Excel for the web, enter it in the cell or formula bar in the same way.
-
Change the range if your values are elsewhere. Replace B2:B5 with the cells that hold your data. For example, if your figures are in C3:C18, use that range in place of the example range. Make sure the first and last references cover all the intended values and do not include the cell where the result will appear.
To add two separate blocks of numbers, use a comma between the ranges. With values in B2:B5 that total 10.376 and D2:D5 that total 4.123, the formula
=ROUND(SUM(B2:B5,D2:D5),2)returns 14.50: the combined sum is 14.499, which rounds to two decimal places as 14.50. This is handy when totals are split across nonadjacent columns or sections. -
Choose the precision you need. In the example formula, change the final 2 to 0 if you want a whole-number total, or to 3 if you want three digits after the decimal point. ROUND uses standard rounding: a following digit of 5 or greater rounds the last kept digit up, while a digit below 5 leaves it unchanged. Negative precision rounds to the left of the decimal point.
For instance, with 412 in B2, 516 in B3, and 336 in B4, the sum is 1,264. The formula
=ROUND(SUM(B2:B4),-2)returns 1,300, rounding the total to the nearest hundred. A precision of -1 would round to the nearest ten instead. -
Confirm the result and display format. Check that the formula returns the expected value. If you want the cell to show two decimal places consistently, select it and use Home > Number to choose a number format or adjust the decimal-place controls. The formula determines the rounded value; number formatting determines how that value is displayed. A cell formatted to show two decimal places can display trailing zeros even when the underlying rounded value has fewer significant digits.
-
Copy the formula where needed. If each row or section needs its own rounded sum, copy the formula to the other result cells and confirm that the references point to the intended data. Excel normally adjusts relative references when you copy a formula. For example, moving a formula down one row typically changes B2:B5 to B3:B6. Inspect a copied formula if the ranges should stay fixed or if the data layout is irregular.
Common Mistakes
Rounding each value when you meant to round the total. ROUND(SUM(range),2) adds the unrounded values and rounds once at the end. That may differ from rounding every row separately and then adding those rounded rows. For example, two values of 1.004 sum to 2.008, which rounds to 2.01. If each value is first rounded to cents, each becomes 1.00 and the combined displayed amounts add to 2.00. Decide which method matches your requirements before building the sheet.
Putting the functions in the wrong order. The intended structure is ROUND wrapped around SUM. A formula that rounds only one cell and then adds other cells does not round the complete total. Check that the closing parentheses place the entire SUM calculation inside ROUND.
Using the wrong precision. ROUND’s second argument is a number of decimal places, not a number of digits in the whole answer. A 2 means two digits after the decimal point; 0 means no decimal places; -2 means the nearest hundred. If a total looks too precise or too coarse, verify this argument first.
Including the answer cell in the sum range. If the formula cell falls inside its own SUM range, Excel may report a circular reference or fail to calculate as expected. Put the result outside the range, or adjust the range so it includes only the input cells.
Expecting formatting to change the calculation. Applying a two-decimal display format can make a long result look rounded without changing the value Excel uses in later calculations. Use ROUND in the formula when the calculated result itself must be rounded; use formatting when you only need to control what is shown on screen.
Tips
Use a clear input range and keep the result in a separate column or row. This makes the formula easier to audit and reduces the risk of accidentally adding a heading, subtotal, or previous result. For a table that grows over time, consider placing the data in an Excel table so new rows are easier to manage; check that the resulting total includes the rows you expect.
Think about the purpose of the values when picking precision. Two decimals are common for currency display, but the appropriate calculation may depend on how the values were created and what rules your organization follows. For measurements, the useful precision depends on the units and required accuracy. Rounding to more decimal places does not make the original data more accurate.
Keep the formula and the number format aligned. If the formula rounds to two decimal places, showing two decimals makes the result easy to read. If your values are counts, a whole-number precision may be clearer. For a rounded hundred, a format with no decimals can make the output less confusing, but remember that the underlying result has already been rounded by the formula.
For workbooks shared with people using different regional settings, be aware that Excel may display a different list separator in formulas depending on the computer’s settings. The examples here use commas, as requested for standard Excel formula syntax. If Excel rejects the formula because of the separator, use the separator your installation expects; this does not change what the formula calculates. On Windows, Excel’s regional settings can affect this behavior; Mac and web behavior can also depend on locale.
If you need to audit a total, calculate the plain sum in a temporary cell and compare it with the rounded result. This helps separate an issue with the range from an issue with the rounding precision. Remove the temporary calculation after checking, or label it clearly so it is not mistaken for the final answer.
Troubleshooting
The formula appears as text instead of calculating. Check whether the cell is formatted as Text. Change the format to General or Number, then edit the cell and press Enter to make Excel parse the formula again. Also confirm that the formula starts with an equals sign and that there is no apostrophe before it.
Excel displays a formula error after you enter it. Check that the parentheses are balanced and that the range uses a colon between its first and last cell. Confirm that the ROUND function has both arguments: the sum to round and the number of decimal places. If the formula uses commas but your regional settings require semicolons, replace the argument separators with the local separator and try again.
The answer is 0 or is smaller than expected. Inspect the selected cells and make sure the range covers the data, not blank cells or an adjacent column. Check for numbers stored as text, which may be ignored by SUM. You can convert text values to numbers by selecting the affected cells and using the warning menu when Excel offers Convert to Number, or by re-entering the values as numbers. Verify that decimal separators match the workbook’s locale.
The displayed total seems wrong by a small amount. Compare the raw sum with the rounded answer and check the final digit before the requested precision. A value that appears as 1.00 on screen may actually contain additional decimal places. Also check whether the intended policy is to round the combined sum once or to round individual entries before totaling them.
You see ##### in the result cell. Excel often displays this when the column is too narrow for the formatted number. Widen the column by dragging its right boundary or double-clicking that boundary to AutoFit. The hashes usually do not mean the SUM or ROUND calculation failed.
Frequently Asked Questions
Can I sum and round in one Excel formula?
Yes. Put SUM inside ROUND so Excel adds the specified cells and then rounds the resulting total. Choose the cell range and decimal-place argument to match your sheet.
Does ROUND(SUM(range),2) round every value first?
No. It sums the values at their stored precision and rounds the final total to two decimal places. To round each entry before adding it, you would need a different calculation approach.
Can I round to a whole number or to the nearest hundred?
Yes. Use 0 as ROUND’s precision argument for a whole number and -2 for the nearest hundred. Negative precision rounds to positions left of the decimal point.
Why does the total look rounded even when I did not use ROUND?
Cell formatting may only be hiding decimal places. Select the cell and increase the displayed decimal places to inspect the value, or use ROUND in the formula if the calculation itself must be rounded.
Can I use this formula in Excel for Mac or the web?
Yes. SUM and ROUND are available in current Excel for Mac and Excel for the web, and the formula structure is the same. Depending on regional settings, the argument separator may differ from the comma shown in the examples.
What if I want to round up rather than use standard rounding?
ROUND follows standard rounding rules. If a process specifically requires always rounding away from zero, look at ROUNDUP; if it requires rounding toward zero, look at ROUNDDOWN. Confirm the required rule before substituting a different function, since it can change the total.
Related
- How to Save an Excel File in Google Sheets
- How to Save an Excel File in Read-Only Mode: Steps
- Excel Rounding Shortcut: Change Decimals or Values
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Sort Dates in Excel in Ascending Order the Right WayTo sort dates in Excel from oldest to newest, select a date cell, then choose Data > Sort…
- PMT Function in Excel: Example and Payment FormulaWith the loan amount in B2, annual interest rate in B3, and term in years in B4, enter…
- Excel Split Function: How TEXTSPLIT Splits Text in CellsExcel does not have a worksheet function named SPLIT; in Microsoft 365 and Excel 2024, use TEXTSPLIT to…
- UNIQUE and FILTER in Excel Tables: A Practical GuideTo return a sorted list of distinct items from an Excel Table that match a condition, enter =SORT(UNIQUE(FILTER(Sales[Item],(Sales[Region]=G2)*(Sales[Item]<>""))))…
- Count Unique Text Values in Excel: Formulas & TipsTo count unique, nonblank text values in Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,ISTEXT(A2:A100)*(A2:A100<>"")))),0) when the values are in…
- VLOOKUP Between Two Excel Sheets: Step-by-StepTo use VLOOKUP between two sheets in the same Excel workbook, enter a formula on the sheet where…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.