The SUMIFS function in Excel allows you to add numbers that meet multiple conditions. It is one of the most useful functions for analyzing business data because it can total sales, expenses, hours, inventory, or any other numeric values based on multiple criteria simultaneously.
If you work with reports, dashboards, budgets, or transaction data, SUMIFS is a must-know Excel function.
In this guide, you’ll learn
- What the SUMIFS function does
- The syntax and arguments of SUMIFS
- How to sum values using multiple conditions
- How to use text, numbers, and date criteria
- How to use operators like greater than and less than
- Common SUMIFS mistakes and how to fix them
- Real-world examples of SUMIFS in Excel
What Is the SUMIFS Function?
The SUMIFS function adds values in a range only when multiple conditions are met.
SUMIFS Function Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2]...)Argument Definitions:
- sum_range — The cells you want to add together
- criteria_range — The first range to evaluate
- criteria1 — The condition to apply
- criteria_range2 — Optional additional range
- criteria2 — Optional additional condition
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> SUMIFS Function Data
Basic SUMIFS Example
Imagine you have a sales table like this
You want to total sales where:
- Sales Rep = Sarah
- Region = West
Use:
=SUMIFS(C2:C5,A2:A5,"Sarah",B2:B5,"West")Excel returns 1400
Because only one row matches both conditions.
How SUMIFS Works
SUMIFS checks every row against all conditions.
A value is only included in the total if every condition is true.
In the example:
=SUMIFS(C2:C5,A2:A5,"Sarah",B2:B5,"West")Excel asks:
- Is the Sales Rep Sarah?
- Is the Region West?
If both answers are YES, Excel adds the sales amount.
Using SUMIFS with Number Conditions
SUMIFS can also evaluate numeric conditions.
For example, sum all sales greater than 1000:
=SUMIFS(C2:C5,C2:C5,">1000")This adds only values above 1000.
You can also combine conditions.
Example:
=SUMIFS(C2:C5,B2:B5,"East",C2:C5,">1000")This totals sales in the East region above 1000.
Using SUMIFS with Dates
SUMIFS is extremely useful for date-based reporting.
For example, total sales between January 5 and January 10:
=SUMIFS(B2:B20,A2:A20,">=1/5/2026",A2:A20,"<=1/10/2026")This formula adds sales between January 5 and January 10.
Common Date Operators
| Operator | Meaning |
|---|---|
>= | Greater than or equal to |
<= | Less than or equal to |
> | Greater than |
< | Less than |
Using Cell References in SUMIFS
Instead of typing conditions directly into formulas, you can reference cells.
Example:
=SUMIFS(C2:C21,B2:B21,F1,A2:A21,F2)Where:
- E1 contains a region
- E2 contains a sales rep
This makes formulas dynamic and easier to update.
If the values in F1 or F2 change, the SUMIFS result updates automatically.
SUMIFS vs SUMIF
| Function | Purpose |
|---|---|
| SUMIF | Sums using one condition |
| SUMIFS | Sums using multiple conditions |
SUMIF Example
=SUMIF(A2:A10,"East",B2:B10)SUMIFS Example
=SUMIFS(C2:C10,A2:A10,"East",B2:B10,"Sarah")If you need more than one condition, use SUMIFS.
Important SUMIFS Rules
All ranges must be the same size
This works:
=SUMIFS(C2:C10,A2:A10,"East")This causes errors:
=SUMIFS(C2:C10,A2:A5,"East")The ranges must contain the same number of rows and columns.
SUMIFS Is AND Logic
SUMIFS uses AND logic by default.
That means every condition must be true.
For example:
=SUMIFS(C2:C20,A2:A20,"East",B2:B20,"Sarah")Excel only sums rows where:
- Region is East
- AND Sales Rep is Sarah
Common SUMIFS Errors
Forgetting quotation marks
Incorrect:
=SUMIFS(C2:C10,A2:A10,East)Correct:
=SUMIFS(C2:C10,A2:A10,"East")Using mismatched ranges
All criteria ranges and the sum range must match in size.
Incorrect operator formatting
Incorrect:
=SUMIFS(C2:C10,C2:C10,>1000)Correct:
=SUMIFS(C2:C10,C2:C10,">1000")Operators must be inside quotation marks.
Real-World Uses for SUMIFS
SUMIFS is commonly used for:
- Sales reporting
- Budget analysis
- Expense tracking
- Payroll calculations
- Inventory management
- KPI dashboards
- Financial reporting
- Project tracking
It is one of the most important reporting functions in Excel.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> SUMIFS Function Data
