The SUM function adds numbers in a range or list of arguments. It returns the total as a single number, making it the most common way to total columns, rows, or scattered cells in Excel.
=SUM(number1, [number2], …)What does SUM do?
The SUM function is Excel's most fundamental math tool. It takes numbers, cell references, or ranges and adds them together, returning a single total. Whether you are totaling a column of expenses, adding up test scores, or summing quarterly sales, SUM does the job in one step.
Why does it matter? Manual addition is slow and error-prone, especially as data grows. SUM updates automatically when values change, works with large ranges, and can combine multiple areas in one formula. It is also the building block for more advanced calculations like averages, budgets, and financial models.
Imagine you are an office worker tracking monthly expenses in a spreadsheet. Instead of typing each number into a calculator, you type =SUM(B2:B13) and instantly get the total. If a value changes, the total updates. This guide walks you through SUM from simple to realistic business scenarios, with clear examples and practical tips.
When to use SUM
- Totaling a column of sales figures for a monthly report
- Adding up student scores to calculate a final grade
- Summing expenses across categories in a budget sheet
- Combining values from multiple sheets or ranges into one total
- Calculating the total hours worked in a timesheet
- Adding a list of invoice amounts to get the grand total
SUM syntax
=SUM(number1, [number2], …)| Argument | Required? | What to enter |
|---|---|---|
number1 | Required | The first number, cell reference, or range to add. Example: B2:B10 or 100. |
number2 | Optional | Additional numbers, references, or ranges to add. You can include up to 255 arguments. Example: C2:C10. |
What it returns: A single number representing the sum of all numeric values in the arguments. Text and empty cells are ignored.
SUM examples
1 Simple column total
You have a list of monthly sales amounts in column B and want the total. The formula =SUM(B2:B6) adds all numbers from B2 to B6. It returns 5500, the sum of 1200, 1500, 900, 1100, and 800.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Sales | |||
| 2 | Jan | 1200 | 5500 | ||
| 3 | Feb | 1500 | |||
| 4 | Mar | 900 | |||
| 5 | Apr | 1100 | |||
| 6 | May | 800 | |||
| 7 |
2 Sum with multiple ranges
You have sales for two regions in separate columns and want the combined total. The formula =SUM(B2:B5, C2:C5) adds all numbers in B2:B5 and C2:C5. It returns 9000, the sum of 1000+1500+1200+1300 (North) and 800+900+1100+1200 (South).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | North | South | |||
| 2 | Widget | 1000 | 800 | 9000 | ||
| 3 | Gadget | 1500 | 900 | |||
| 4 | Doohickey | 1200 | 1100 | |||
| 5 | Thingamajig | 1300 | 1200 | |||
| 6 |
3 Sum with a condition using SUM and IF (array formula)
You want to sum only the sales for the 'North' region. The formula =SUM(IF(A2:A6="North", B2:B6, 0)) checks each region in A2:A6; if it is 'North', it includes the corresponding B value, otherwise 0. It returns 2500 (1200+1300). In modern Excel, this works as a dynamic array; in older versions, enter with Ctrl+Shift+Enter.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 1200 | 2500 | ||
| 3 | South | 800 | |||
| 4 | North | 1300 | |||
| 5 | East | 900 | |||
| 6 | West | 1100 | |||
| 7 |
4 Sum with mixed data types (text and numbers)
Your data includes both numbers and text labels in the same range. The formula =SUM(B2:B6) adds only the numeric values and ignores text. It returns 4500 (1000+1500+2000), skipping 'N/A' and 'TBD'.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Amount | |||
| 2 | Rent | 1000 | 4500 | ||
| 3 | Utilities | 1500 | |||
| 4 | Supplies | N/A | |||
| 5 | Repairs | 2000 | |||
| 6 | Misc | TBD | |||
| 7 |
Common SUM errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | One of the arguments is text that cannot be interpreted as a number, and you are not using a range (e.g., =SUM("abc", 5)). | Ensure all arguments are numbers or valid cell references. Use SUM with ranges to ignore text automatically. |
| #REF! | A referenced cell or range has been deleted or moved, leaving an invalid reference. | Check the formula for broken references and update them to valid ranges. |
| #NAME? | The function name is misspelled (e.g., =SUMM(A1:A5)) or the analysis toolpak is missing (rare). | Correct the spelling to SUM. Ensure Excel recognises the function. |
| Incorrect total (not an error value) | Numbers stored as text are ignored by SUM, leading to a lower total than expected. | Convert text numbers to real numbers using Value or Text to Columns, or use SUMPRODUCT with --. |
Tips for using SUM
- Use
SUMwith whole-column references likeB:Bfor expanding datasets, but be aware it includes the header if numeric. - Press
Alt+=to quickly insert an AutoSum formula for the adjacent column or row. - To sum only visible cells (e.g., after filtering), use
SUBTOTAL(109, range)instead ofSUM. - Combine
SUMwithIFfor conditional sums, or useSUMIFSfor multiple criteria. - Format the result cell as Currency or Number to make totals readable.
- Double-check that ranges do not overlap when using multiple arguments to avoid double-counting.
Frequently asked questions
What is the difference between SUM and SUMIF?
SUM adds all numbers in a range, while SUMIF adds only numbers that meet a specified condition. Use SUMIF when you need to total values based on criteria, such as a specific region or date.
Can SUM add numbers from different sheets?
Yes. You can use a 3D reference like =SUM(Sheet1:Sheet3!B2:B10) to add the same range across multiple sheets. Alternatively, list each sheet reference separated by commas.
Why does SUM return 0 when there are numbers?
The numbers might be stored as text. SUM ignores text values. Check the cell alignment (text is left-aligned by default) and convert them to numbers using Value or Text to Columns.
Does SUM work with dates?
Yes, dates are stored as serial numbers in Excel, so SUM can add them. However, adding dates rarely makes sense; use it to sum durations or numeric date values instead.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. SUM reference