To sum multiple cells in Excel, use the SUM function: enter =SUM(A1:A5) to add the values in cells A1 through A5. For cells that are not next to one another, list each cell or range inside the same function, such as =SUM(A1,A3,A5), then press Enter.
How Excel adds cells
Excel can add values with a formula or with the AutoSum command. A formula updates when its referenced cells change, so it is usually more useful than typing a total directly into a cell. The SUM function accepts individual cell references, ranges, or a mixture of both. A range uses a colon between its first and last cell references; for example, A1:A5 means every cell from A1 through A5.
For a simple example, suppose cells B2 through B6 contain 12, 8, 15, 5, and 10. With those values in B2:B6, the formula =SUM(B2:B6) returns 50. If you add 3 to B6, the result changes to 53 automatically. The formula itself stays in the result cell, while Excel recalculates its value.
SUM is generally preferable to manually adding a short list because it is easier to extend, read, and audit. It also makes a long list of cells manageable: the formula can refer to a range rather than requiring a separate reference for every cell.
Sum a continuous range
When the cells are adjacent, use a colon to specify the full range. For example, with weekly amounts in C2:C8, =SUM(C2:C8) adds all seven cells. The endpoints are included, as are the cells between them. If your table has a heading in C1, starting at C2 avoids including the heading in the calculation.
You can also select a range with the mouse while building a formula:
- Select the cell where the total should appear and type
=SUM(. - Drag across the cells to add, or click the first cell and Shift-click the last cell.
- Type a closing parenthesis and press Enter. Excel may add the parenthesis automatically as you select the range.
Use a range that includes the intended data but not the total cell itself. For example, placing a total in B7 and summing B2:B7 creates a circular reference because the formula would include its own result. Sum B2:B6 instead.
Sum cells that are not next to one another
If the values are separated by labels, blank cells, or other data, include the required references as separate arguments. With amounts in B2, B4, and B7, and values 12, 15, and 9 in those cells, =SUM(B2,B4,B7) returns 36. Commas separate the arguments in Excel formulas configured for the standard English-language list separator.
You can mix individual cells and ranges. Suppose the amounts to include are B2:B5 and D2:D5, while column C contains information that should not be added. With the desired numbers in those two blocks, use =SUM(B2:B5,D2:D5). This adds both blocks without including column C.
For just two or three cells, direct addition is also possible. With values in B2, B4, and B7, =B2+B4+B7 returns the same total as the SUM example. Direct addition is easy to read for a tiny calculation, but SUM is typically clearer when the list grows or includes ranges. A common mistake is writing a range with a comma, such as B2,B7, when the intention is to include every cell between them. Use B2:B7 for a continuous range; use commas to separate distinct arguments.
Use AutoSum instead of typing the formula
AutoSum can create a SUM formula for a nearby row or column of numbers. In Excel for Microsoft 365 on Windows, select the empty cell below a column of values or to the right of a row, then choose Home > AutoSum. Check the highlighted range Excel proposes and press Enter. The command is convenient, but it can guess the wrong range if the data has blank cells or nearby totals, so review the selection before accepting it.
On Windows, Alt+= is the keyboard shortcut for AutoSum. On a Mac, use the AutoSum command on the ribbon if the Windows shortcut does not apply; keyboard shortcuts can differ with the keyboard layout and Excel version. In Excel for the web, AutoSum is also available from the ribbon, though the interface and shortcuts can vary by browser and platform.
AutoSum is a quick way to insert a standard total, not a different kind of calculation. It ordinarily inserts a SUM formula, which you can edit afterward. For totals that need to skip filtered-out rows, choose SUBTOTAL instead, as described below.
Choose the right formula for the task
Use the simplest method that matches the layout and rules of your data:
- One uninterrupted block: SUM with a range, such as
=SUM(C2:C20). - Scattered cells or separate blocks: SUM with multiple arguments.
- A few values and no need for a range: direct addition can be readable, but it is less convenient to maintain.
- Values that meet a condition: SUMIF for one condition or SUMIFS for multiple conditions.
- A filtered list where hidden rows should not count: SUBTOTAL with its 109 function number for a sum that excludes manually hidden rows as well as filtered-out rows.
For example, ordinary SUM does not change its result just because a filter hides some records. If a filtered sales list should total only the visible records, use SUBTOTAL rather than assuming SUM will follow the filter. The distinction matters in reports where users filter by region, date, or category.
Sum values that meet a condition
When you need a total for only one category, SUMIF checks a criteria range and adds matching values from a corresponding sum range. With categories in A2:A6 and amounts in B2:B6, and the word Travel in A2, A4, and A6, =SUMIF(A2:A6,"Travel",B2:B6) adds the amounts on those rows. The criteria range and sum range should cover corresponding rows so that each category is matched with its amount.
For more than one condition, use SUMIFS. With regions in A2:A20, status labels in B2:B20, and amounts in C2:C20, =SUMIFS(C2:C20,A2:A20,"East",B2:B20,"Completed") adds amounts only where the region is East and the status is Completed. In SUMIFS, the sum range comes first, followed by each criteria range and its criterion. This order differs from SUMIF, where the criteria range comes before the sum range.
Criteria can be text, numbers, or comparisons. For a comparison such as greater than 100, the criterion is written as text inside quotation marks: =SUMIF(B2:B20,">100"). With values in B2:B20, this adds only values greater than 100. If you want a condition to refer to another cell, join the comparison operator to that reference with an ampersand, for example =SUMIF(B2:B20,">"&D1). These criteria formulas are useful for summaries, but a plain SUM remains easier when every selected value should count.
Filtered lists and visible rows
Excel’s filter feature hides rows from view without removing them from the worksheet. A normal SUM over a filtered column still includes values in rows hidden by the filter. To total just the visible records, use SUBTOTAL. With amounts in B2:B20, =SUBTOTAL(109,B2:B20) sums visible values and ignores rows excluded by a filter; function number 109 also excludes rows hidden manually.
The number 9 is another SUBTOTAL option for summing, but it does not exclude manually hidden rows in the same way. The 109 option is often the clearer choice when the goal is a visible-only total. SUBTOTAL also ignores other SUBTOTAL formulas in its reference range, which helps avoid counting intermediate subtotals twice. As with any formula, make sure the range matches the data and does not include unrelated values.
Common problems and how to fix them
If a total looks too small or returns zero, check whether the entries are real numbers or text that merely looks numeric. SUM ignores text in referenced cells, including numbers stored as text. A warning indicator, inconsistent alignment, or an imported-data source can point to this issue. Convert the affected values to numbers before relying on the total, and verify that the result updates as expected.
If the formula returns an error, inspect the referenced cells. An error value such as #N/A or #VALUE! in a cell included in a SUM range can make the calculation return an error too. Also check for a circular reference, such as a total formula that includes its own cell, and confirm that all parentheses and range references are correct.
A formula may appear as text instead of calculating if the cell is formatted as Text or if an apostrophe was entered before the equals sign. Change the cell format to General, then re-enter the formula. If Excel displays the formula rather than its result across the sheet, the Show Formulas display may be on; pressing Ctrl+` on Windows toggles that view. Mac keyboards and layouts can differ, so use the Show Formulas control on the Formulas tab if the shortcut is unavailable.
Another source of confusion is the separator character. In many English-language Excel installations, commas separate function arguments. Regional settings can use semicolons instead. If a formula copied from another system is rejected, check the list separator configured for that Excel installation rather than changing the function name.
Make totals easier to maintain
For a changing list, an Excel table can make formulas more manageable. Select the data and choose Insert > Table, then confirm whether the table has headers. When you add rows to a table, its range generally expands with the data, and a Total Row can be enabled from the Table Design tab. Table formulas may display structured references rather than cell coordinates; these are still formulas and can make it easier to see which column is being totaled.
For a simple worksheet that will not grow, a direct range such as B2:B20 is often sufficient. If rows will be appended regularly, avoid choosing an unnecessarily broad range that could include unrelated figures. A table or a carefully maintained range makes the total easier for another person to inspect and reduces the chance that newly added records are left out.
Frequently Asked Questions
What is the formula to sum multiple cells in Excel?
Use =SUM(A1:A5) to add a continuous range, replacing A1:A5 with your cells. For separate cells, list them as arguments, such as =SUM(A1,A3,A5).
How do I add cells from different columns?
Put each cell or range in the same SUM function. For example, if the values to include are in B2:B5 and D2:D5, use =SUM(B2:B5,D2:D5). This skips column C.
Can I sum cells that are not next to each other?
Yes. Separate individual cell references or ranges with commas inside SUM. A colon means all cells between two endpoints, while a comma separates distinct references or blocks.
Why does SUM return zero when cells contain numbers?
The entries may be stored as text, which SUM does not treat as numeric values in a referenced range. Convert them to numbers and check that the formula points to the correct cells.
Does SUM ignore filtered-out rows?
No. SUM includes values in filtered-out rows. Use =SUBTOTAL(109,B2:B20) when B2:B20 contains the values and you want a total of visible rows that also excludes manually hidden rows.
Summary
For most totals, use SUM with a continuous range, such as =SUM(B2:B6). List separate cells or ranges as additional arguments when the values are scattered, and choose SUMIF, SUMIFS, or SUBTOTAL when the total depends on criteria or filtered visibility. Check the referenced range, number formats, and result cell whenever a total does not match expectations.
Related
- Excel Data Entry: Enter and Manage Cell Values
- Excel Military Time: Convert and Format It
- Excel TEXT Percentage: How to Format Values as Percentages
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Unique in Excel: Get Values Without DuplicatesTo list each value once in Excel for Microsoft 365, enter =UNIQUE(A2:A10) in an empty cell, replacing A2:A10…
- Excel Formula for Checking If a Date Is Before Another DateTo check whether one date is earlier than another in Excel, use the less-than operator: with the date…
- AVERAGEIF Across Multiple Excel Sheets: Formulas & TipsYes, you can average values that meet a condition across multiple Excel sheets, but a single ordinary AVERAGEIF…
- Excel Consolidate Blank Cells Into One Cell With TEXTJOINYes—if by “blank wells” you mean blank cells between values, Excel can combine the nonblank entries into one…
- Excel Formula to Split Text in a Cell: A Practical GuideIn Excel for Microsoft 365, split text in a cell with =TEXTSPLIT(A2,","), replacing the comma with the character…
- Excel INDEX MATCH With Table Structured ReferencesYes—Excel INDEX MATCH works with table structured references. For example, if a table named Inventory has columns Item…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.