SUM - Add numbers with confidence
Turn a column of sales or expenses into a reliable total. Learn how ranges work, spot common mistakes, and put SUM into practice.
By the end, you can
- Write a SUM formula using cells and ranges.
- Build and check a sales or expense total.
- Recognize text, missing values, and circular references.
Before you start: Know how to select a cell and enter a number. Cell and range references are explained below.
In this lesson
What SUM does
SUM adds numbers and returns one total. Use it when you need combined revenue, an invoice total, a monthly budget, or stock across branches. Instead of typing every amount again, point SUM at the cells that already contain your data.
=SUM(B2:B6)Add every numeric value from B2 through B6, including both endpoints.
Syntax and arguments
=SUM(number1, [number2], …)Start with =. Put the values or references inside parentheses. Square brackets and the ellipsis describe optional arguments; do not type them into your formula.
| Argument | Purpose | Example |
|---|---|---|
number1Required | The first number, cell reference, or range to add. | B2:B6 |
number2, …Optional | More numbers, cells, or ranges. Excel allows up to 255 arguments in total; a range is one argument even when it contains many cells. | D2:D6, 500 |
Read cells and ranges
- B2 is one cell: column B, row 2.
- B2:B6 is five cells: B2, B3, B4, B5, and B6. The colon means from the first cell through the last.
- B2,B6 supplies two separate arguments. It does not include B3, B4, or B5.
- B2:C6 is a rectangle covering both columns and all five rows.
References such as B2:B6 are relative: copying the formula one column right changes them to C2:C6. Use $B$2:$B$6 when the range must stay fixed as you copy. The dollar signs lock the column and row.
Step by step: regional sales
A distributor tracks this week's sales in INR. Column B contains actual sales; column C contains targets. Total actual sales in B7 without including targets.
₹2,11,600| A | B | C | |
|---|---|---|---|
| 1 | Region | Sales | Target |
| 2 | North | ₹52,000 | ₹50,000 |
| 3 | South | ₹43,000 | ₹45,000 |
| 4 | East | ₹38,500 | ₹40,000 |
| 5 | West | ₹46,700 | ₹44,000 |
| 6 | Central | ₹31,400 | ₹32,000 |
| 7 | Total | ₹2,11,600 |
- Select B7, the empty result cell below the sales figures.
A second example: monthly expenses
An office administrator totals monthly running costs before comparing spending with a ₹30,000 budget. The same formula works because the amounts occupy the same range.
₹28,000| A | B | |
|---|---|---|
| 1 | Expense | Amount |
| 2 | Rent | ₹15,000 |
| 3 | Electricity | ₹2,400 |
| 4 | Internet | ₹900 |
| 5 | Supplies | ₹6,500 |
| 6 | Transport | ₹3,200 |
| 7 | Total | ₹28,000 |
=SUM(B2:B6)Subtract ₹28,000 from the ₹30,000 budget to find ₹2,000 remaining.
Different ways to use SUM
=SUM(B2:B6)The usual choice for a column of amounts in the sales example.
=SUM(B2,B5,B6)Add North, West, and Central only. Other rows are left out.
=SUM(B2:B3,B5:B6)Add two groups, leaving out East. Avoid overlapping ranges: a cell in both ranges is counted twice.
=SUM(100,250,300)Useful for a quick calculation. Cell references are easier to maintain in recurring reports.
=SUM(B2:B6,-5000)Negative numbers reduce the total; here a ₹5,000 adjustment is deducted.
Blanks, text, and errors
When SUM reads cells, it adds numeric values and ignores blanks, text, and logical values (TRUE or FALSE). A blank is not an error, but a missing amount can still make a business total incomplete. If there are no numeric cells, the result is 0.
An error in an input cell, such as #N/A or #DIV/0!, propagates to the SUM result. Fix the source error instead of hiding it and reporting an incomplete total.
SUM includes numbers in filtered-out and manually hidden rows. For a total that follows visible rows, investigate SUBTOTAL. Excel dates are stored as numbers, so accidentally including a date column can distort a total.
Common mistakes and fixes
Shortcuts and practical tips
- In Excel for Windows, select the result cell and press Alt + = for AutoSum. Verify the suggested range before pressing Enter; blanks can interrupt the selection.
- AutoSum is also available from Excel's Home or Formulas tab. Keyboard shortcuts vary by platform.
- Use a continuous range instead of a long chain such as =B2+B3+B4. It is easier to audit and handles text in referenced cells without a #VALUE! error.
- Apply INR formatting after entering numeric amounts. Formatting changes display, not the underlying value.
- In desktop Excel, an Excel Table and structured references can make recurring reports easier to extend. Always check new rows are included.
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.