The easiest way to sum numbers in Excel is to select the blank cell beneath or beside them and choose Home > AutoSum (Σ), then press Enter. For a formula you can control directly, type =SUM(A2:A10) using the cells that contain your numbers.
Use AutoSum for a quick total
AutoSum is useful when a column or row already contains the values you want to add. In Excel for Microsoft 365 on Windows, click the empty cell immediately below a column of numbers, then select Home > AutoSum. Excel inserts a SUM formula and highlights the range it thinks you want to total. Check that the highlighted cells are correct, then press Enter to accept the formula.
For example, if the amounts are in A2 through A5 and the total belongs in A6, selecting A6 and using AutoSum should propose the four values above it. The arithmetic is 120 + 85 + 95 + 40 = 340. AutoSum is a shortcut for inserting a formula, not a separate kind of calculation: after accepting it, you can click the total cell and see or edit the formula in the formula bar.
On Windows, the keyboard shortcut for AutoSum is Alt+=. On a Mac, use Formulas > AutoSum if you prefer the ribbon; menu and shortcut behavior can differ by keyboard layout and Excel version. In Excel for the web, the ribbon’s AutoSum command is also available. If Excel selects an incomplete range or includes a label, adjust the selection before pressing Enter.
Write a SUM formula yourself
The SUM function is the standard choice when you want a clear, reusable total. It can add a continuous range, separate ranges, or individual numbers. A colon between two cell references means the range includes the cells between them. The formula updates when values in its referenced cells change.
With sales amounts in A2:A5, the formula to total the four cells is =SUM(A2:A5). If those cells contain 120, 85, 95, and 40, the returned result is 340. The same formula can be entered directly in a blank cell, or created by selecting the cell and using the formula bar.
SUM accepts more than one argument. With January amounts in B2:B10 and February amounts in C2:C10, a total across both months can be written as =SUM(B2:B10,C2:C10). It returns the sum of all values in both ranges. A continuous rectangular block can also be summed: if the monthly amounts are in B2:C10, =SUM(B2:C10) includes every cell in that block. Use the form that best communicates which data belongs in the total.
When entering a formula, start with an equals sign. Excel formulas use commas to separate function arguments in the standard US-style syntax shown here; some regional settings use semicolons instead. If Excel rejects an otherwise correct formula, check the list separator configured for your regional settings.
Choose the right range and keep totals reliable
A sum is only as dependable as its references. Before accepting AutoSum or writing a formula, make sure the range includes every intended value and excludes headers, notes, and existing subtotals. A range such as A2:A10 includes both endpoints and every cell between them; it does not mean only A2 and A10.
For a growing list, you may want the total to include rows added later. One option is to use a generous range that includes likely future entries, such as A2:A100, provided no unrelated values are likely to be entered in that area. A more structured option is to convert the data to an Excel table using Insert > Table. When a table has a Total Row enabled from the Table Design tab, Excel can provide a sum for a column and adjust the table as rows are added. Tables are especially helpful for lists that are regularly extended.
Another frequent issue is placing the total inside the range it sums. If a total is in A10, a formula that includes A10 itself creates a circular reference. Put the total outside the selected range—for example, values in A2:A9 and the total in A10—or carefully exclude the total cell from the formula. A circular reference warning is a sign to inspect the formula’s references, not to repeat the calculation.
Add only values that meet a condition
When the goal is not to add every number, use a conditional sum. SUMIF adds values that match one criterion; SUMIFS can apply multiple criteria. These functions are useful for questions such as “What were the sales in the North region?” or “How much did the North team sell in March?”
Suppose region names are in A2:A6, amounts are in B2:B6, and D2 contains the word North. The formula =SUMIF(A2:A6,D2,B2:B6) adds amounts in B2:B6 only where the corresponding region in A2:A6 matches D2. If the rows are North 120, South 80, North 60, East 40, and North 30, the result is 210. The criteria range and sum range should cover corresponding rows so each amount is checked against its own region.
For two conditions, suppose regions are in A2:A6, months are in B2:B6, amounts are in C2:C6, and D2 and E2 contain the desired region and month. The formula =SUMIFS(C2:C6,A2:A6,D2,B2:B6,E2) sums the amounts where both the region matches D2 and the month matches E2. SUMIFS places the sum range first, followed by pairs of criteria ranges and criteria. That argument order differs from SUMIF, so check it when building the formula.
Criteria can be typed directly into formulas, but putting them in cells makes a workbook easier to update. Change the region or month in the criteria cells and the total recalculates without editing the formula. For criteria involving comparisons, wildcards, or dates, confirm that the comparison is written in a form Excel can interpret and that the underlying values are stored consistently.
Sum visible rows in a filtered list
A regular SUM includes values in rows hidden by a filter. That may be correct for an overall total, but it can surprise someone who filters a table and expects the displayed rows alone to be counted. For a filtered list, SUBTOTAL is often more appropriate because it can exclude rows hidden by filtering.
For example, if amounts are in B2:B20 and the list is filtered, =SUBTOTAL(9,B2:B20) adds visible values while excluding rows removed by the filter. The function number 9 specifies SUM. If you also want to exclude rows hidden manually, use 109 instead: =SUBTOTAL(109,B2:B20). These formulas return a total based on the rows currently visible under the filter, so changing the filter can change the result.
SUBTOTAL is not a universal replacement for SUM. Use SUM when hidden rows should still count, such as when a hidden row is merely out of sight but remains part of the full report. Use SUBTOTAL when the displayed subset is what the total should represent. If the filtered range contains nested SUBTOTAL formulas, Excel excludes those nested subtotals from the calculation to avoid counting them again.
Why a SUM formula may show an unexpected result
If a total is zero or too small, check whether the values are actually numbers. Text that looks like a number—perhaps imported with a leading apostrophe, spaces, or an incompatible decimal separator—may not be included by SUM. A quick check is to select a suspicious cell and inspect its alignment and formula bar, though formatting alone does not prove whether the stored value is numeric. Convert or clean imported data as needed before relying on the total.
If the result is too large, inspect the range for duplicated records, unintended rows, and existing totals. A formula that includes a subtotal row can double-count the values represented by that subtotal. Also check whether AutoSum selected an adjacent block that was not part of the intended calculation. Click the formula cell and review the colored reference outlines while editing the formula.
Excel normally recalculates formulas when referenced values change. If a displayed total appears stale, check Formulas > Calculation Options and make sure calculation is set to Automatic. Manual calculation can leave formulas showing old results until recalculation is requested. Also check for errors such as #VALUE! in a referenced calculation: an error in a SUM range can cause the total itself to show an error rather than a number.
Finally, number formatting changes how a result looks, not the arithmetic itself. A total displayed with no decimal places may be rounded visually even though the underlying value includes cents. Select the result and increase decimal places or inspect the formula bar if you need to see the stored precision.
When to use AutoSum, SUM, or a conditional total
Choose AutoSum when speed matters and the data sits in a simple, clearly bounded row or column. It is convenient for a one-off total, but the automatically proposed range should always be checked. Choose SUM when you want an explicit formula that is easy to review, copy, or combine with several ranges. Choose SUMIF or SUMIFS when the total should include only records meeting one or more criteria.
For a filtered report, choose SUBTOTAL if the total should change with the visible rows. For an ongoing dataset, consider an Excel table rather than continually editing a fixed range. The best approach depends on what the number is meant to represent: all stored values, a selected group of values, or only the rows currently shown. Clarifying that question prevents many spreadsheet mistakes.
Frequently Asked Questions
What is the fastest way to add a column in Excel?
Select the blank cell below the numbers, choose Home > AutoSum, check the highlighted range, and press Enter. On Windows, Alt+= opens AutoSum for the selected cell.
What is the formula to add cells in Excel?
Use SUM with the cell range. With the values to add in A2 through A10, enter =SUM(A2:A10) in a different cell. The formula adds the values in that range and recalculates when they change.
Can Excel add cells that are not next to each other?
Yes. SUM accepts separate references or ranges separated by commas. With values in A2:A5 and C2:C5, for example, =SUM(A2:A5,C2:C5) adds both groups and skips the cells between them.
Does SUM include hidden or filtered-out rows?
Yes, ordinary SUM includes values in hidden rows. If you want a filtered list’s visible values only, use SUBTOTAL with function number 9; use 109 when manually hidden rows should also be excluded.
Why is my Excel sum showing zero?
One possibility is that the cells contain numbers stored as text, which SUM may ignore. Check the data for imported text, spaces, or decimal separators that do not match your settings, and verify that the formula points to the intended cells.
Related
- Contact List Format in Excel: Columns, Setup, and Tips
- Contact List Excel Sheet: How to Build and Use One
- Contact List Excel Spreadsheet: Create and Manage It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Contact List Template: Build, Sort, and MaintainAn Excel contact list template is a workbook with clearly labeled columns for names, contact details, categories, and…
- Using MID in Excel: Extract Text by Position and LengthUse Excel’s MID function to return a chosen number of characters from a text value, starting at a…
- Excel MIN Ignore NA: Formulas That WorkYes. In Excel for Microsoft 365, use =AGGREGATE(5,6,A2:A10) to find the smallest numeric value in A2:A10 while ignoring…
- Hide a Column If a Cell Is Blank in ExcelYes—but Excel has no built-in setting that automatically hides a column when a cell becomes blank. In Excel…
- Excel IFERROR Formula Examples: Uses and Common FixesIf the value is in A2 and the divisor is in B2, use =IFERROR(A2/B2,"Cannot divide") to show a…
- How to Find a Z Value in Excel: Formulas and ExamplesTo calculate a z-score for a value in Excel, subtract the dataset mean from the value and divide…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.