SUM is the most used formula in Excel. Here's everything you need — syntax, 6 real examples, common mistakes, and practice exercises.
SUM adds up a list of numbers. That's it. But because adding numbers is one of the most common tasks in any spreadsheet, SUM ends up being the formula people use more than any other.
Without SUM, adding ten numbers would look like this: =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10. With SUM it's just: =SUM(A1:A10). And if you had 1,000 rows, SUM would be just as easy to write.
=SUM(number1, [number2], [number3], ...)
| Argument | Description |
|---|---|
| number1 required | The first number, cell reference, or range to add. This is almost always a range like B2:B10. |
| number2 ... optional | Additional numbers or ranges to include. You can add up to 255 separate arguments. |
You have monthly sales in B2:B13 and want the annual total.
=SUM(B2:B13)This adds every value in column B from row 2 to row 13. If a cell is empty it's ignored — SUM only counts numbers.
You want to total Q1 (B2:B4) and Q3 (B8:B10) but not Q2 or Q4.
=SUM(B2:B4, B8:B10)Separate ranges with a comma. SUM will add all of them together in one formula.
Revenue is in column B and a bonus in column D. Add both for a total.
=SUM(B2:B10, D2:D10)Show a cumulative total that grows with each row. In C2, enter:
=SUM($B$2:B2) ← anchor the start, let the end move
Copy this formula down. The $B$2 stays fixed, but B2 changes to B3, B4 etc., giving you a running total that adds each new row.
If you only want to add values that meet a condition, use SUMIF — not SUM with IF. For example, to total only sales above 1000:
=SUMIF(B2:B10, ">1000")If data keeps being added to a column and you always want the full total, reference the entire column:
=SUM(B:B) ← adds every number in column B
Be careful — this includes the header row if it accidentally contains a number. Better practice is to use a fixed range like B2:B1000 to leave room for growth.
If SUM returns 0 or a wrong result, your numbers might be stored as text. Look for a green triangle in the top-left of the cell — that's Excel's warning. Use the VALUE() function or paste-as-values to convert them to real numbers.
SUM includes hidden rows in its total. If you've filtered your data and only want to sum the visible rows, use =SUBTOTAL(9, B2:B10) instead — it respects filters.
If you put a SUM formula in a cell that's inside the range it's summing, Excel will show a circular reference error. Make sure the SUM result cell is outside the range being summed.
Select the cell below a column of numbers and press Alt + = (Windows) or Cmd + Shift + T (Mac). Excel automatically inserts a SUM formula with the correct range.
You don't need to worry about blank cells or text labels in your range — SUM skips them automatically. Only numeric values are included in the total.
Use SUM for straightforward totals. Use SUBTOTAL when you need to respect filters. Use AGGREGATE when you need to ignore errors or hidden rows in more complex situations.
=SUMPRODUCT(VALUE(B2:B10)) as a workaround.=SUM(Sheet1!B2:B10, Sheet2!B2:B10). Or use a 3D reference to sum the same range across multiple sheets: =SUM(Sheet1:Sheet3!B2:B10).=SUM(VLOOKUP(A2,table,2,0), VLOOKUP(A3,table,2,0)). Or in newer Excel, use XLOOKUP which can return multiple values that SUM can add directly.Reading about SUM takes 5 minutes. Remembering it in a job interview requires having typed it yourself. ExcelPro's exercises put you in a live spreadsheet with realistic data — free to start.
Try SUM exercises free →