Math & aggregation
How to Use SUMIFS in Excel (Sum with Multiple Criteria)
SUMIFS adds up numbers that meet two or more conditions at the same time: sales for the West region in January, paid invoices over 100, amounts within a date range.
The big gotcha is argument order: in SUMIFS the sum_range comes FIRST, the reverse of SUMIF. This guide covers text, numeric, and date criteria with copy-ready formulas.
SUMIFS syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) | Argument | Required | What it does |
|---|---|---|
sum_range | Required | The cells to add. In SUMIFS this comes FIRST, the opposite of SUMIF. |
criteria_range1 | Required | The first range to test against a condition. |
criteria1 | Required | The condition for range 1: "West", ">100", or ">="&E1. |
criteria_range2 | Optional | A second range to test (and so on, up to 127 pairs). |
criteria2 | Optional | The condition for range 2. All conditions must be TRUE (AND logic). |
Note: The sum_range goes first, the reverse of SUMIF. Works the same in Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Two text conditions
=SUMIFS(C:C, A:A, "West", B:B, "January") Adds values in C only where both A equals West AND B equals January. SUMIFS combines conditions with AND, never OR.
2. Number plus text condition
=SUMIFS(D:D, A:A, "Paid", D:D, ">100") A criteria range can be the same column as the sum_range: here D is both summed and tested for >100.
3. Sum within a date range
=SUMIFS(C:C, B:B, ">="&DATE(2026,1,1), B:B, "<="&DATE(2026,12,31)) Two conditions on the same date column create a between filter. Join each operator to DATE() with & so Excel reads it as a comparison.
How to write SUMIFS step by step
- 1
Click the result cell and type =SUMIFS(
- 2
Select the range of numbers to add (the sum_range) FIRST, then a comma.
- 3
Select the first range to test, a comma, then its condition in quotes, then a comma.
- 4
Add more range/criteria pairs the same way for each extra condition.
- 5
Close the bracket ) and press Enter.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
Wrong total | sum_range was placed last (SUMIF order) instead of first. | Put the range to add FIRST in SUMIFS: =SUMIFS(sum_range, range1, crit1, ...). |
Result is 0 | A criteria does not match: a number stored as text, or stray spaces. | Confirm the test columns and criteria are the same type; clean text with TRIM(). |
#VALUE! | sum_range and a criteria_range are different shapes or sizes. | Make every range the same number of rows and the same orientation. |
Operator ignored | A comparison such as >E1 was written without joining the cell reference. | Concatenate the operator and reference with &, e.g. ">="&E1. |
Frequently asked questions
What is the difference between SUMIF and SUMIFS?
SUMIF handles one condition with the sum range last; SUMIFS handles many conditions with the sum range FIRST: =SUMIFS(sum_range, range1, crit1, range2, crit2).
Can SUMIFS use OR logic?
No. SUMIFS only does AND (all conditions must be true). For OR, add two SUMIFS together, or use SUMPRODUCT.
How do I sum between two dates with SUMIFS?
Test the same date column twice: =SUMIFS(C:C, B:B, ">="&DATE(2026,1,1), B:B, "<="&DATE(2026,12,31)).
Does SUMIFS work in Google Sheets?
Yes. SUMIFS works the same in Google Sheets, including the sum_range-first argument order.