To add numbers in a column in Excel, use the SUM function. If the values are in A2 through A10, enter =SUM(A2:A10) in an empty cell; Excel returns their total and updates it when the included values change.
The basic SUM formula
SUM adds numbers from one or more cells or ranges. In the example above, the colon means “through,” so the range includes every cell from A2 to A10. The result appears in the cell containing the formula, not in the cells being added.
For a small check, suppose A2:A6 contains 12, 8, 15, 5, and 10. With those values in A2:A6, =SUM(A2:A6) returns 50. If you change A4 from 15 to 20, the total recalculates to 55. You can type a formula into a cell or select the cell, type the formula in the formula bar, and press Enter.
SUM can also add separate ranges or individual cells. For example, if the amounts to include are in A2, A4, and A7, then =SUM(A2,A4,A7) adds only those three cells. In formulas with multiple arguments, Excel for Microsoft 365 on Windows uses commas between arguments. The colon denotes a continuous range; it is not an argument separator.
Sum an entire column
If you expect data to be added throughout a column, you can refer to the full column instead of guessing its last row. With amounts in column B and the total placed in a different column, such as C1, use =SUM(B:B). This totals numeric values anywhere in B, including values added later.
A full-column reference is convenient for an ongoing list, but it also includes every cell in that column. Avoid putting the formula in column B itself: the formula would include its own cell in the sum and create a circular reference. Place the result in another column, or use a bounded range such as B2:B5000 when the data area is known. A bounded range can make the intended input area clearer, while a full-column reference needs less maintenance as rows are appended.
For a normal list with a header in B1 and values beneath it, you may instead place the result below the data and sum the known data cells. If B2:B5 contains 120, 85, 95, and 100, then =SUM(B2:B5) returns 400. This range deliberately excludes the header and the total cell below it.
Use AutoSum instead of typing
AutoSum inserts a SUM formula based on nearby data. In Excel for Microsoft 365 on Windows, click the blank cell below a continuous list of numbers, then choose Formulas > AutoSum. Excel proposes a range; check that the highlighted cells are the ones you intend to add, then press Enter. You can also press Alt+= on Windows to insert AutoSum.
AutoSum is useful for a quick total, but its suggested range is not a guarantee. A blank row, a text label, or another nearby number can affect what Excel selects. Inspect the highlighted range before accepting it, especially if the worksheet contains multiple blocks of data.
To total a row instead, select the blank cell to the right of the row and use AutoSum; Excel generally proposes the cells to the left. On a Mac, use the AutoSum command on the ribbon rather than relying on the Windows Alt+= shortcut. In Excel for the web, AutoSum is also available from the ribbon, though the layout can differ slightly from the desktop app.
Choose the right range as data grows
A formula is only as complete as the cells it includes. If the list currently runs from A2 to A20 but new entries will be added below row 20, a formula limited to A2:A20 will not automatically include an entry in A21. A full-column reference or an Excel Table can help prevent that kind of missed total.
For a fixed report, a bounded range is often easiest to read and audit. For a growing list, convert the data to a Table by selecting a cell in the data and choosing Insert > Table, then confirm whether the range has headers. Tables expand when data is entered directly in the next row, and their formulas can refer to named columns rather than row numbers.
For example, if the table is named Sales and its numeric column is named Amount, the formula =SUM(Sales[Amount]) adds that table column. This structured reference is easier to interpret than a long cell range and adjusts as the table grows. If you rename the table or column, Excel updates references in formulas that use those names.
Sum only rows that meet a condition
Sometimes the goal is not to add every value in the column. SUMIF adds values that match one condition. Suppose region names are in A2:A20 and corresponding sales amounts are in B2:B20. To total sales for East, use =SUMIF(A2:A20,"East",B2:B20). If the matching rows contain 125, 80, and 95, the result is 300.
The first range is where Excel looks for the condition, the quoted text is the condition, and the final range contains the values to add. The two ranges should line up row by row. A typo or extra space in a label can cause a row not to match, so check the source values if the total seems too low. SUMIF is appropriate for one condition; when totals must meet several conditions, Excel also has SUMIFS.
Handle filtered and hidden rows
SUM includes values in rows hidden by a filter. That means a standard column total can be larger than the sum of the records currently visible on screen. If you want a subtotal that changes with filtering, use SUBTOTAL instead.
With values in B2:B100, =SUBTOTAL(109,B2:B100) adds visible values and excludes both filtered-out rows and manually hidden rows. The function number 109 requests a sum with manually hidden rows excluded. By contrast, =SUBTOTAL(9,B2:B100) excludes rows removed by a filter but includes manually hidden rows. Put either formula outside the range being subtotaled so it cannot include itself.
This distinction matters when reviewing a filtered sales report: a SUM result represents all included data rows, while the 109 version represents the rows left visible after filtering. SUBTOTAL also avoids double-counting other SUBTOTAL formulas in a referenced range, which can be useful for grouped lists.
Common reasons a total looks wrong
- The formula misses newer rows. Check the range shown in the formula bar. If it ends before the last entry, extend the range or use a Table or suitable full-column reference.
- Some values are stored as text. SUM adds numeric values, not text that merely looks like a number. A leading apostrophe, imported data, or inconsistent formatting can leave entries as text. Use Excel’s error indicators or convert the affected cells to numbers, then recalculate.
- A cell contains an error. An error value in the referenced range, such as #N/A or #VALUE!, can make the SUM result return an error. Find and correct the source error rather than hiding it if the underlying value matters.
- The total includes filtered-out records. This is expected with SUM. Use SUBTOTAL with the appropriate function number if the desired result is limited to visible rows.
- The formula creates a circular reference. A full-column sum placed in that same column includes its own formula cell. Move the total elsewhere or restrict the range to cells above the total.
- The displayed total seems rounded. Cell formatting can show fewer decimal places than the stored result. Increase the displayed decimal places or inspect the formula bar before assuming the underlying calculation is wrong.
When checking a questionable result, click the formula cell and inspect the colored range outline Excel displays. This is a quick way to see whether the intended cells are included. Also check whether the workbook is set to calculate automatically under Formulas > Calculation Options; manual calculation can leave results temporarily out of date until recalculation.
SUM compared with adding cells directly
You can add cells with plus signs, but SUM is usually clearer for a continuous column. If values are in A2:A10, =A2+A3+A4+A5+A6+A7+A8+A9+A10 produces a total, but it is longer and easier to leave out a cell when the list changes. The equivalent =SUM(A2:A10) makes the range explicit and is simpler to extend.
Direct addition can still make sense for a few deliberately separate cells, such as values in A2 and C2 with B2 excluded. SUM can express the same selection with separate arguments, so choose the form that makes the intended inputs easiest to verify. For a large list, use a range rather than typing every cell address.
Frequently Asked Questions
What is the formula to add a column in Excel?
If the numbers are in A2:A100, enter =SUM(A2:A100) in a blank cell. Change the range to match the first and last cells containing the values you want to total.
How do I sum a whole column in Excel?
With the result outside column B, use =SUM(B:B) to add numeric values throughout column B. Do not put this full-column formula in column B itself, because it would include its own cell.
Can Excel sum only the visible cells?
Yes. If the values are in B2:B100, =SUBTOTAL(109,B2:B100) sums rows visible after filtering and ignores manually hidden rows as well. A regular SUM includes filtered-out values.
Why is my Excel SUM formula returning zero?
One common cause is that the apparent numbers are stored as text, which SUM does not add as numeric values in a referenced range. Check the cells’ contents and convert text-formatted numbers to numbers. Also confirm that the formula’s range includes the cells you expect.
How do I keep a column total updated when I add rows?
Use an Excel Table and sum its named column, or choose a full-column reference if the formula can sit outside that column. A fixed range such as A2:A20 will not include new entries below row 20 unless you expand it.
Related
- Excel PivotTable Testing: Checks, Steps, and Common Fixes
- Text to Date in Excel: Convert Dates Stored as Text
- Search an Excel Column for Matching Text
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Data Validation Custom Formula Examples That WorkExcel custom data validation lets you accept an entry only when a formula returns TRUE. In Microsoft 365…
- Outlook Address Examples and How to Choose the Right OneCommon Outlook address examples include first.last@outlook.com, initials.number@outlook.com, and a work address such as first.last@company.com. The first two show…
- Count Unique Strings in Excel: Formulas and ExamplesTo count distinct, nonblank text strings in Excel for Microsoft 365, put your data in A2:A100 and use…
- Excel Sort Entire Row by One Column Without Splitting DataYes. To sort entire rows by one column in Excel for Microsoft 365 on Windows, select the full…
- Copy a Formula in Excel: Keyboard ShortcutsTo copy a formula in Excel on Windows, select its cell and press Ctrl+C, select the destination cell…
- How to Find Repeated Names in Excel with COUNTIF FormulaTo find repeated names in Excel, put names in A2:A100, then enter =COUNTIF($A$2:$A$100,A2) in B2 and fill it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.