To calculate a total in Excel, use the SUM function: with amounts in B2:B10, enter =SUM(B2:B10) in an empty cell. If B2, B3, and B4 contain 12, 8, and 5 and the rest are blank, the result is 25.
SUM is usually the clearest way to add a range, and Excel’s AutoSum button can insert it for you. The right approach depends on whether you need a simple column total, totals from separate ranges, a total that responds to filters, or a sum based on conditions.
Use SUM to add a range
The SUM function adds numbers and cell references supplied to it. The colon in a reference such as B2:B10 means every cell from B2 through B10, including both endpoints. Blank cells and text entries in the selected range are not added; numeric values are.
For a concrete example, assume B2:B5 contain 14, 21, 9, and 6. Enter this in another cell:
=SUM(B2:B5)
The result is 50, because 14 + 21 + 9 + 6 = 50. Put the formula outside the range being added—for example, in B6—to keep the result from including itself and creating a circular reference.
You can also supply individual numbers or cell references, separated by commas. This is useful when amounts are in separate parts of a worksheet rather than one continuous block. For example, with numbers in B2:B5 and D2:D5, where the first range totals 50 and the second totals 30, use:
=SUM(B2:B5,D2:D5)
The result is 80. In a typical Microsoft 365 installation, Excel formulas use commas between arguments. Some regional settings use semicolons instead; if Excel rejects a formula copied from another locale, check the list separator configured for that installation.
Calculate a total with AutoSum
AutoSum is a quick way to total a row or column without typing the function and range yourself. In Excel for Microsoft 365 on Windows:
- Select the empty cell directly below a list of numbers, or to the right of a row of numbers.
- Choose Home > AutoSum in the Editing group. You can also choose Formulas > AutoSum.
- Check the range Excel outlines. If it is correct, press Enter; if it is not, select the intended cells before pressing Enter.
AutoSum inserts a SUM formula, so the result updates when the referenced numbers change. It guesses the range by looking for nearby data and blank cells. That guess is convenient, but it is not proof that every intended value was included. A blank row between entries, an adjacent column of numbers, or a total already in the data can affect the suggested range.
On Windows, Alt+= invokes AutoSum in many desktop Excel configurations. On Mac, the equivalent is Command+Shift+T. Keyboard shortcuts can vary with keyboard layout and Excel settings, so the ribbon button is a reliable alternative. Excel for the web also has AutoSum on the ribbon, though shortcuts can depend on the browser and whether browser shortcuts take priority.
Choose the right range and check the result
A correct SUM formula can still produce an unexpected total if its range does not match the data. Before accepting a result, look at the colored reference outline Excel shows while you edit the formula. Confirm the first and last cells, and check whether the range includes a header, a previous total, or blank rows that separate sections.
For a long column, clicking the formula cell and reviewing the formula bar is often faster than visually checking every value. You can also select the cells you want to inspect: Excel displays a Sum in the status bar at the bottom of the window when the selection contains numeric values. This status-bar total is a quick check, not a worksheet formula; it does not create a result cell that can be used elsewhere.
When a range will grow over time, consider converting the data into an Excel Table using Insert > Table. A formula using a table column reference can be easier to maintain as rows are added. With a table named SalesData and a column named Amount, enter the following formula outside the table to total that column:
=SUM(SalesData[Amount])
If the Amount values currently add to 275, the formula returns 275 and includes new table rows added to that column. Table names and column headings must match the workbook exactly, including spaces and punctuation.
Sum multiple ranges or separate values
SUM can combine several ranges and individual cell references in one calculation. This can be useful for a report with monthly figures in separate columns, or for adding totals from distinct sections of a worksheet. Use a comma between each range or reference in the formula.
For example, suppose January figures in B2:B4 add to 120 and February figures in D2:D4 add to 180. With those values in the stated ranges, place this formula in an empty cell:
=SUM(B2:B4,D2:D4)
The result is 300. This method is appropriate when the ranges are deliberately separate. If the values actually occupy one continuous block, use one range instead; a single range is easier to read and less likely to omit an intervening row.
Sum only values that meet a condition
When a total should include only certain records, use SUMIF for one condition or SUMIFS for multiple conditions. A common example is totaling sales for one region while keeping all regions in the same table.
Assume A2:A6 contains region names and B2:B6 contains the corresponding sales amounts. If the East rows contain 120, 80, and 50, and other regions contain other amounts, use:
=SUMIF(A2:A6,"East",B2:B6)
The result is 250: only the sales values whose matching region is East are included. SUMIF takes the range to check, the criterion, and then the range to add. The criterion can also refer to a cell, which is helpful when a user should be able to change the selected region without editing the formula.
For two conditions, suppose A2:A6 contains regions, B2:B6 contains product names, and C2:C6 contains sales. If rows matching East and Widget have sales of 40 and 60, this formula returns their combined total:
=SUMIFS(C2:C6,A2:A6,"East",B2:B6,"Widget")
The result is 100. SUMIFS starts with the sum range, followed by each criteria range and its corresponding criterion. For exact text matches, make sure spelling and extra spaces are consistent. Criteria can also use comparison operators such as greater than or less than, but text criteria must be enclosed in quotation marks within the formula.
Total visible rows in a filtered list
A normal SUM includes values in rows hidden by a filter. If a user wants a displayed subtotal that changes as a list is filtered, SUBTOTAL is often more suitable. For a filtered sales list in B2:B10, enter this formula in a cell outside the list:
=SUBTOTAL(9,B2:B10)
With all visible values totaling 1,000, the result is 1,000; after filtering out rows worth 300, it returns 700. Function number 9 means SUM and excludes rows removed by a filter. However, it still includes rows hidden manually. To exclude manually hidden rows as well as filtered-out rows, use function number 109:
=SUBTOTAL(109,B2:B10)
SUBTOTAL is useful for filtered reports, but it is not interchangeable with SUM in every situation. In particular, its behavior is designed around subtotal calculations and hidden rows. If the worksheet is not filtered and every listed value should count, SUM is simpler and communicates the intent clearly.
Why an Excel total may look wrong
If the result is zero or lower than expected, check the data and formula rather than assuming Excel has made an arithmetic error. Common causes include:
- Numbers stored as text: Values imported from a website or another system may look like numbers but be stored as text. SUM ignores text in a referenced range. Check for warning indicators, inconsistent alignment, or a number format that does not behave as expected.
- An incomplete range: The formula may stop one row too early or leave out a separate column. Edit the formula and confirm the highlighted references.
- A filtered list: SUM includes values in filtered-out rows. Use SUBTOTAL when the intended result should reflect only visible rows.
- Hidden or excluded data: A manually hidden row is still included by SUM. Decide whether hidden rows should count and choose the function accordingly.
- Errors in the range: If a referenced cell contains an error such as #VALUE! or #N/A, SUM generally returns that error rather than a numeric total. Find and resolve the source error before relying on the result.
- Calculation settings: If inputs change but the answer does not, check whether calculation is set to Automatic under Formulas > Calculation Options. In Manual mode, formulas may need recalculation.
Do not “fix” a total by changing its displayed decimal places alone. A cell may show 25 even when the underlying result is 24.997 because of number formatting. Formatting changes what is displayed, not the stored value used in later calculations.
Format and verify the total
After calculating a sum, format it so readers can interpret it correctly. Select the result cell and use the number-format controls on Home to choose Number, Currency, or another appropriate format. Increase or decrease displayed decimal places as needed. Formatting a result as Currency does not convert text into numbers or change the arithmetic; it only changes presentation.
For a report, make the total easy to distinguish with a bold font, a top border, or a descriptive label such as Total sales. Keep that label outside the cells included in the SUM range. If the total feeds another calculation, leave the result as a numeric value rather than typing a currency symbol directly into the cell. Applying a number format preserves the value as a number.
For a final check, compare the formula’s range with the source data and, when practical, compare the result with the status-bar Sum for the same selected cells. If those checks disagree, investigate whether the formula has a different range, the status bar is including a different selection, or some entries are text or hidden.
Frequently Asked Questions
What is the formula to get a total in Excel?
Use SUM with the cells you want to add. For example, if the values are in B2 through B10, enter =SUM(B2:B10) in another cell. Check that the range includes every intended value and does not include the result cell itself.
How do I total a column without typing a formula?
Select the empty cell below the numbers and choose Home > AutoSum. Check the suggested range, then press Enter. AutoSum creates a SUM formula, which updates when its referenced values change.
Why does SUM ignore some numbers?
Some entries may be stored as text rather than numeric values, or they may be outside the formula’s range. Check the cell contents and the highlighted references. SUM does not add text in a referenced range, even if the text looks like digits.
Does SUM include filtered-out rows?
Yes. SUM adds values in filtered-out rows as well as visible ones. Use SUBTOTAL with function number 9 to total filtered visible rows, or 109 if manually hidden rows should also be excluded.
Can I add cells that are not next to each other?
Yes. Include multiple ranges or individual references in SUM, separated by commas. For example, =SUM(B2:B5,D2:D5) adds two separate blocks. Confirm that each listed range is intentional, especially when the worksheet layout changes.
Why does a total show #######?
This usually means the column is too narrow to display the formatted value, rather than that the sum itself is invalid. Widen the column or choose a shorter number format. If widening does not resolve it, inspect the cell’s format and the formula result.
Related
- Excel Mail Merge: Create Letters, Labels, and Emails
- Excel Daylight Saving Time: How Dates and Hours Work
- Monthly Excel Budget Format: Build a Simple Template
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Column Headers Changed to Numbers? How to Fix ItIf Excel’s column headings show numbers instead of letters, the R1C1 reference style is turned on. In Microsoft…
- Excel Circular Reference Error: How to Find and Fix ItIf Excel keeps showing a circular reference error, find the formula loop and change one reference so the…
- OneNote Find Tags Not Working? Common Causes and FixesIf OneNote’s Find Tags feature is not showing the notes you expect, first check that you are using…
- Average Function in Excel Not Working? Fix the CauseIf Excel’s AVERAGE function is not working, first check that the range contains numbers Excel recognizes and that…
- TRIM Excel Not Working? Fix Spaces That TRIM MissesIf TRIM appears not to work in Excel, the text may contain a character that TRIM does not…
- SUMIF Not Working With Text? Fix Common Criteria IssuesYes—Excel’s SUMIF can use text as its criterion, such as a department name or product label, while adding…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.