The SUMIF function in Excel allows you to add numbers that meet a specific condition. Instead of summing an entire range, SUMIF lets you total only the values that match the criteria you choose.
This is one of the most useful functions in Excel because real-world data often needs to be filtered before calculating totals. Whether you’re adding sales by region, expenses by category, or hours worked by employee, SUMIF helps you calculate totals quickly and accurately.
In this guide, you’ll learn:
- What the SUMIF function does
- How to write a SUMIF formula
- How to sum values that match text criteria
- How to sum values based on numbers
- How to use comparison operators in SUMIF
- Common mistakes to avoid
- Real-world examples of SUMIF in action
What Does the SUMIF Function Do?
The SUMIF function adds numbers in a range only if they meet a specific condition.
Instead of adding everything, SUMIF asks:
“Does this value match the condition?”
If yes → Add it
If no → Ignore it
This makes SUMIF extremely powerful for working with filtered totals without manually sorting or filtering your data.
SUMIF Function Syntax
Here is the structure of the SUMIF function:
=SUMIF(range, criteria, [sum_range])
Argument Breakdown
range
The range of cells that Excel checks against the condition.
criteria
The condition that determines which cells to include.
sum_range (optional)
The cells that actually get added together.
If sum_range is omitted, Excel sums the range instead.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> SUMIF Function Data
SUMIF With Text Criteria
Let’s start with a simple dataset showing sales by region.
We want to add only the sales from the East region.
Click in an empty cell and enter:
=SUMIF(A2:A7,"East",B2:B7)
Press Enter.
Excel will return:
3800
That is the total of all East region sales.
Understanding How This Formula Works
Let’s break it down:
=SUMIF(A2:A7,"East",B2:B7)
A2:A7
Excel checks this range for the condition.
“East”
This is the condition.
B2:B7
These are the numbers that get added.
Excel checks each row:
If Region = East → Add Sales
If not → Skip it
SUMIF with Numbers
SUMIF isn’t limited to text. It also works with numbers.
Let’s add quantities greater than 15.
Enter:
=SUMIF(G2:G6,">15")
Excel adds only numbers greater than 15.
Another way to write the formula is below. Seems a little redundant, but can be easier to troubleshoot if you’re new to Excel formulas.
=SUMIF(G2:G6,">15",G2:G6)
Using Comparison Operators in SUMIF
SUMIF supports comparison symbols. In our previous formula, we used the greater than sign to add all quantities greater than 15.
| Operator | Meaning |
|---|---|
| > | Greater than |
| < | Less than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
| <> | Not equal to |
Using Cell References as Criteria
Instead of typing criteria directly, you can reference another cell.
If K1 contains East, then use
=SUMIF(A2:A6,K1,B2:B6)
This makes your spreadsheet more flexible.
Change K1 → Formula updates automatically.
Real-World SUMIF Example
Imagine tracking monthly expenses.
To total only Food expenses, use:
=SUMIF(A2:A6,"Food",B2:B6)
Excel returns:
625
This is extremely useful for budgeting and financial tracking.
Common SUMIF Mistakes to Avoid
Using Mismatched Ranges
Incorrect:
=SUMIF(A2:A7,"East",B2:B6)
Ranges must be the same size.
Forgetting Quotes Around Text
Incorrect:
=SUMIF(A2:A7,East,B2:B7)
Correct:
=SUMIF(A2:A7,"East",B2:B7)
Incorrect Comparison Operators
Always use quotes:
Correct:
">1000"
Incorrect:
>1000
Why SUMIF Is So Important
SUMIF is one of the most widely used Excel functions because it:
- Eliminates manual filtering
- Saves time on calculations
- Makes reports dynamic
- Supports real-world data analysis
When Should You Use SUMIF?
Use SUMIF anytime you need to:
- Add values that match a category
- Total numbers based on conditions
- Create filtered summaries
- Analyze grouped data
It is especially useful in:
- Sales reports
- Budgets
- Inventory tracking
- Performance summaries
SUMIF vs SUM — What’s the Difference?
| Function | Purpose |
|---|---|
| SUM | Adds everything |
| SUMIF | Adds only matching values |
Think of SUMIF as:
SUM with rules
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> SUMIF Function Data
