SUMIFS adds the numbers in one range only when the matching cells in one or more other ranges meet all your conditions. It is the go-to function for multi-condition totals such as sales by region and month.
=SUMIFS(sum_range, criteria_range1, criteria1, ...)What does SUMIFS do?
SUMIFS is Excel's multi-condition adding machine. You give it a range of numbers to add, then pairs of ranges and criteria. Excel checks every row: if all the criteria are true, the number in that row is included in the total. If any condition fails, the row is skipped. The result is a single number, such as total sales for the North region in January.
The function matters because real questions rarely have one condition. Managers ask for revenue by product and quarter, hours by employee and project, or expenses by department and category. Filtering and manually adding is slow and easy to get wrong. SUMIFS answers those questions in one formula that updates automatically when the data changes.
A typical situation: you maintain a sales log with date, region, product and amount. Your manager wants the total for the East region in March for product A. Instead of sorting and selecting, you write =SUMIFS(D2:D100, A2:A100, "East", B2:B100, "March", C2:C100, "A") and the answer appears instantly. Change a criterion and the total recalculates.
When to use SUMIFS
- Total sales for a region within a date range
- Sum hours worked by employee and project
- Add expenses for a department and category
- Calculate revenue by product and quarter
- Count-like totals such as units sold by store and month
- Add invoice amounts for a customer with a status of Paid
SUMIFS syntax
=SUMIFS(sum_range, criteria_range1, criteria1, ...)| Argument | Required? | What to enter |
|---|---|---|
sum_range | Required | The cells containing the numbers to add. Only rows where all criteria match are included. Example: D2:D100 holds the amounts. |
criteria_range1 | Required | The first range to test. It must be the same size as sum_range. Example: A2:A100 holds regions. |
criteria1 | Required | The condition for criteria_range1. Can be text, a number, a cell reference, or an operator in quotes such as ">100". Example: "East". |
criteria_range2, criteria2, ... | Optional | Additional range-and-condition pairs. Each pair must match for a row to be included. You can use up to 127 pairs. Example: B2:B100, ">=2024-01-01". |
What it returns: A single number: the sum of the values in sum_range for every row where all criteria are true. If no rows match, it returns 0.
SUMIFS examples
1 Example 1: Total sales for one region
The goal is to add all sales amounts for the North region. The formula checks each row in column A for "North"; when it matches, the value in column B is added. Rows for other regions are ignored. The result is the combined North sales figure, 3,700.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 1200 | 3700 | ||
| 3 | South | 800 | |||
| 4 | North | 1500 | |||
| 5 | East | 950 | |||
| 6 | North | 1000 | |||
| 7 | West | 700 | |||
| 8 |
2 Example 2: Sales above a threshold in one region
Here you want North region sales that are greater than 1,000. SUMIFS uses two conditions: region equals "North" and sales greater than 1000. The comparison operator must be inside quotes. Only rows 2 and 4 qualify, giving 1,200 + 1,500 = 2,700.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 1200 | 2700 | ||
| 3 | South | 800 | |||
| 4 | North | 1500 | |||
| 5 | East | 950 | |||
| 6 | North | 1000 | |||
| 7 | West | 700 | |||
| 8 |
3 Example 3: Sales by region and month
You need total sales for the East region in March. The formula tests column A for "East" and column B for "March". Both conditions must be true in the same row. Only row 4 matches, so the result is 1,100.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Month | Sales | |||
| 2 | North | January | 1200 | 1900 | ||
| 3 | East | February | 900 | |||
| 4 | East | March | 1100 | |||
| 5 | North | March | 1500 | |||
| 6 | West | March | 700 | |||
| 7 | East | March | 800 | |||
| 8 |
4 Example 4: Sales for a region within a date range
This example adds North region sales between 2024-01-01 and 2024-01-31 inclusive. Dates are compared with operators in quotes. The formula checks region equals "North", date greater than or equal to 2024-01-01, and date less than or equal to 2024-01-31. Rows 2 and 3 match, giving 1,200 + 1,500 = 2,700.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Date | Sales | |||
| 2 | North | 2024-01-05 | 1200 | 0 | ||
| 3 | North | 2024-01-20 | 1500 | |||
| 4 | South | 2024-01-10 | 800 | |||
| 5 | North | 2024-02-02 | 1000 | |||
| 6 | East | 2024-01-15 | 950 | |||
| 7 | North | 2024-01-31 | 700 | |||
| 8 |
5 Example 5: Paid invoices for one customer
A finance team wants the total amount of paid invoices for customer Acme. The formula checks column A for "Acme" and column C for "Paid". Only rows 2 and 4 qualify, so the total is 1,200 + 1,500 = 2,700. Unpaid or other-customer invoices are excluded.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Customer | Invoice | Status | Amount | |||
| 2 | Acme | INV-001 | Paid | 1200 | 3000 | ||
| 3 | Acme | INV-002 | Unpaid | 800 | |||
| 4 | Acme | INV-003 | Paid | 1500 | |||
| 5 | Beta | INV-004 | Paid | 950 | |||
| 6 | Beta | INV-005 | Unpaid | 700 | |||
| 7 | Acme | INV-006 | Paid | 300 | |||
| 8 |
Common SUMIFS errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | A criteria range is a different size from sum_range, or a text criterion was entered without quotes. | Make all ranges the same number of rows and columns. Put text and operators in double quotes, for example ">100". |
| #NAME? | The function name is misspelled, such as SUMIF or SUMIFS with a space. | Type =SUMIFS( exactly, or start typing and choose SUMIFS from the formula list. |
| Wrong total (too high or too low) | Criteria ranges and sum_range are misaligned, or a criterion uses the wrong operator or spelling. | Check that each criteria range lines up row-for-row with sum_range. Test one condition at a time and confirm spelling and operators. |
| Dates do not match | Dates are stored as text rather than real dates, so comparisons fail. | Convert the date column to real dates with Date Value or Text to Columns, then use criteria such as ">=2024-01-01". |
| 0 returned unexpectedly | No rows meet all conditions, often because of extra spaces or different case in text criteria. | Trim spaces with TRIM, check for hidden characters, and confirm the criteria text matches the data exactly. |
Tips for using SUMIFS
- Use cell references for criteria, such as
=SUMIFS(B2:B100, A2:A100, F2), so you can change the condition without editing the formula. - Wildcards work in text criteria:
"North*"matches North, Northeast and so on. Use?for a single character. - Keep sum_range and every criteria range the same size. Named ranges or Excel Tables make this easier and safer.
- For dates, use operators in quotes:
">="&DATE(2024,1,1)is clearer than typing a date string. - To add several criteria on the same range, repeat the range:
=SUMIFS(C2:C100, A2:A100, "North", A2:A100, "East")returns 0 because a cell cannot be both. - Remember SUMIFS adds numbers only. If sum_range contains text, those cells are ignored.
Frequently asked questions
What is the difference between SUMIF and SUMIFS?
SUMIF handles one condition and puts the sum range last. SUMIFS handles one or more conditions and puts the sum range first. Use SUMIFS for anything with two or more conditions.
Can SUMIFS use dates as criteria?
Yes. Put the operator and date in quotes, such as ">=2024-01-01", or build it with ">="&DATE(2024,1,1). The date column should contain real dates, not text.
Why does my SUMIFS return 0?
Usually no row meets every condition. Check spelling, extra spaces, whether dates are real dates, and whether all ranges are the same size. Test each condition separately to find the culprit.
Does SUMIFS work with wildcards?
Yes, for text criteria. * matches any number of characters and ? matches one character. For example, "A*" sums rows starting with A.
Can I sum values between two numbers with SUMIFS?
Yes. Use the same range twice with two criteria, such as =SUMIFS(B2:B100, B2:B100, ">=100", B2:B100, "<=500").
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. SUMIFS reference