Need to add up numbers based on multiple conditions? SUMIFS handles this in one formula. Instead of nesting IFs or using helper columns, you can filter and sum your data simultaneously.
What Makes SUMIFS Useful
SUMIFS takes the guesswork out of conditional totals:
- Multiple criteria – Test up to 127 different conditions at once
- Different columns – Each condition can check a different column
- Flexible operators – Use equals, greater than, less than, or text matching
- Large datasets – Faster than filtering manually or using array formulas
- Date ranges – Perfect for summing values between two dates
The Syntax
=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2], ...)The sum_range comes first, then pairs of criteria ranges and their conditions.
Example 1: Sales by Region
You need to total sales for the West region:
| Product | Region | Sales |
|---|---|---|
| Laptop | West | $4,500 |
| Mouse | East | $250 |
| Keyboard | West | $800 |
| Monitor | West | $1,200 |
Formula: =SUMIFS(C2:C5, B2:B5, "West")
Result: $6,500
Sum column C where column B equals “West”. Straightforward filtering and adding.
Example 2: Multiple Conditions
Total sales for laptops in the West region only:
| Product | Region | Sales |
|---|---|---|
| Laptop | West | $4,500 |
| Mouse | West | $250 |
| Laptop | East | $4,200 |
| Laptop | West | $4,800 |
Formula: =SUMIFS(C2:C5, A2:A5, "Laptop", B2:B5, "West")
Result: $9,300
Both conditions must be true. Product must be “Laptop” AND region must be “West”.
Example 3: Numeric Criteria
Sum all sales over $1,000:
| Product | Region | Sales |
|---|---|---|
| Laptop | West | $4,500 |
| Mouse | East | $250 |
| Keyboard | West | $800 |
| Monitor | West | $1,200 |
Formula: =SUMIFS(C2:C5, C2:C5, ">1000")
Result: $5,700
Put comparison operators in quotes. You can use >, <, >=, <=, or <>.
Example 4: Date Ranges
Total sales from January 2025:
| Date | Product | Sales |
|---|---|---|
| 1/5/2025 | Laptop | $4,500 |
| 12/28/2024 | Mouse | $250 |
| 1/15/2025 | Keyboard | $800 |
| 1/22/2025 | Monitor | $1,200 |
Formula: =SUMIFS(C2:C5, A2:A5, ">=1/1/2025", A2:A5, "<2/1/2025")
Result: $6,500
Use the same range twice with different criteria to create a date range. Between January 1st and February 1st.
Example 5: Using Cell References
Sum sales for a region specified in cell E1:
| Product | Region | Sales |
|---|---|---|
| Laptop | West | $4,500 |
| Mouse | East | $250 |
| Keyboard | West | $800 |
Formula: =SUMIFS(C2:C4, B2:B4, E1)
Result: Depends on what’s in E1
No quotes needed when referencing another cell. This makes your formulas dynamic.
Example 6: Wildcard Matching
Sum all products that start with “Lap”:
| Product | Region | Sales |
|---|---|---|
| Laptop | West | $4,500 |
| Laptop Case | East | $50 |
| Mouse | West | $250 |
| Laptop Stand | West | $120 |
Formula: =SUMIFS(C2:C5, A2:A5, "Lap*")
Result: $4,670
Use * for multiple characters or ? for single characters. Great for partial text matches.
Common Operators
| Operator | Meaning | Example |
|---|---|---|
| = | Equal to | “West” |
| > | Greater than | “>1000” |
| < | Less than | “<500” |
| >= | Greater than or equal | “>=1/1/2025” |
| <= | Less than or equal | “<=100” |
| <> | Not equal to | “<>East” |
Quick Tips
- All ranges must be the same size
- Criteria ranges can be different columns
- Put operators and dates in quotes
- Cell references don’t need quotes
- Text criteria are case-insensitive
Start Using It
Open a spreadsheet with some data. Pick two or three columns to filter by. Write a SUMIFS formula. You’ll start using it everywhere once you see how much time it saves.
SUMIFS turns complex filtering tasks into single formulas.
Questions about SUMIFS? Want help applying it to your data? Let’s connect.