The SUM function in Excel is one of the most commonly used formulas and one of the first functions every Excel user should learn. It allows you to quickly add numbers together without manually calculating totals.
In this guide, you’ll learn:
- What the SUM function does
- How to write a SUM formula
- How to sum a range of cells
- How to use SUM with non-adjacent cells
- Common mistakes to avoid
- Real-world examples of SUM in action
By the end of this tutorial, you’ll be able to use the SUM function in Excel for everyday tasks confidently.
What the SUM Function Do in Excel
The SUM function adds numbers together. Instead of manually typing each number into a calculator, Excel can automatically total values stored in cells.
For example, if cells B2 through B6 contain numbers, the SUM function can add them instantly.
The syntax for the SUM function is:
=SUM(number1, [number2], ...)
Where:
- number1 — The first number or range to add
- number2 — Optional additional numbers or ranges
Most commonly, you’ll sum a range of cells, like this:
=SUM(B2:B6)
This tells Excel to add every number from cell B2 through B6.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> SUM Function Data
How to Use the SUM Function to Add a Range
The most common way to use SUM is to total a column of numbers.
Follow these steps:
- Click cell B8
- Type:
=SUM(B2:B6)
- Press Enter
Excel will add all numbers from B2 through B6 and display the total.
How to Use SUM with Non-Adjacent Cells
Sometimes you need to add numbers from cells that are not next to each other.
For example, you might only want to total Pens, Markers, and Tape.
You can do this by listing individual cells separated by commas.
Example formula:
=SUM(B2,B4,B6)
This adds values from:
- B2
- B4
- B6
How to Use AutoSum in Excel
Excel includes a built-in AutoSum feature that automatically creates a SUM formula.
To use AutoSum:
- Click cell B8
- Go to the Formulas tab
- Click AutoSum
- Press Enter
Excel will automatically select the range B2:B6.
SUM Multiple Ranges at Once
The SUM function can total multiple ranges in one formula.
Example:
=SUM(B2:B4,B5:B6)
This adds:
- B2 through B4
- B5 through B6
Common SUM Errors and How to Fix Them
Even though SUM is simple, a few common mistakes can cause incorrect results.
Numbers Stored as Text
If numbers are stored as text, SUM will ignore them.
Signs numbers are stored as text:
- Numbers aligned to the left
- Green triangle warning icon
Fix:
- Select the cells
- Click the warning icon
- Choose Convert to Number
Including Blank or Wrong Cells
If the wrong range is selected, totals may be incorrect.
Always verify:
=SUM(B2:B6)
matches your intended range.
Typing Instead of Selecting
Typing ranges manually increases the chance of mistakes.
Best practice:
Use your mouse to select the range instead of typing it.
Real-World Uses of the SUM Function
The SUM function is used in nearly every type of spreadsheet.
Common uses include:
- Adding monthly expenses
- Calculating total sales
- Summing employee hours
- Tracking inventory totals
- Creating financial summaries
For example, a budget spreadsheet might use the sum function to total all expenses.
Download the Practice Workbook
You can download the sample data we’ll be using in this tutorial by clicking on this link —> SUM Function Data
