SUMIFS, multiple criteria.
D2SUMIFS syntax
SUMIFS adds up the cells in a range that meet all of several conditions. Unlike SUMIF, the sum range comes first:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)- sum_range — the values to add up.
- criteria_range1, criteria1 — the first condition (range + the test it must pass).
- [criteria_range2, criteria2], … — optional further conditions, combined with AND.
All ranges must be the same size, or you get a #VALUE! error.
Examples
1. Two criteria
Sum sales in column C where the month in A is January and the region in B is West:
=SUMIFS(C:C, A:A, "January", B:B, "West")2. A date range (between two dates)
Sum amounts in C where the date in A is on or after E1 and on or before E2:
=SUMIFS(C:C, A:A, ">=" & E1, A:A, "<=" & E2)Join the operator and the cell with the & sign so Excel reads it as one criterion.
3. A number threshold
Sum order values in C where the quantity in D is greater than 100:
=SUMIFS(C:C, D:D, ">100")4. Wildcards (partial text)
Sum amounts in B where the description in A contains the word "invoice":
=SUMIFS(B:B, A:A, "*invoice*")5. OR logic (match any of several values)
SUMIFS combines conditions with AND. For OR, add SUMIFS results together:
=SUMIFS(C:C, A:A, "West") + SUMIFS(C:C, A:A, "East")Common SUMIFS errors
#VALUE! — mismatched range sizes
Every range in SUMIFS must have the same number of rows. =SUMIFS(C2:C100, A2:A50, "West") fails because C2:C100 and A2:A50 differ. Make them identical.
Criteria text not matching
Leading/trailing spaces or text-vs-number mismatches stop a criterion from matching. Clean the data with TRIM() and confirm both sides are the same type.
SUMIFS in Google Sheets and Apple Numbers
SUMIFS works identically in Excel, Google Sheets, and Apple Numbers. In US Excel the argument separator is a comma; in many European locales it is a semicolon — FormulaAI outputs the right separator for your version automatically.
Generate SUMIFS with FormulaAI
Describe the total you need in plain English — "sum sales where region is West and month is January" — and FormulaAI returns the full SUMIFS formula for your platform, with a step-by-step explanation.
Frequently asked questions
What is the difference between SUMIF and SUMIFS?
SUMIF sums a range based on a single condition. SUMIFS sums based on one or more conditions, and its argument order is different: SUMIFS puts the sum_range first, then pairs of criteria_range and criteria. Use SUMIFS whenever you have two or more conditions.
How do I use SUMIFS with a date range?
Add two criteria on the same date column: =SUMIFS(C:C, A:A, '>=' & E1, A:A, '<=' & E2) sums values in C where the date in A is between E1 and E2. Use the & operator to join the comparison operator with the cell holding the date.
Can SUMIFS use OR logic (match any of several values)?
SUMIFS combines its criteria with AND, not OR. For OR logic, add two SUMIFS together — =SUMIFS(C:C,A:A,'West')+SUMIFS(C:C,A:A,'East') — or use SUMPRODUCT for more complex OR conditions.
Why does SUMIFS return a #VALUE! error?
The most common cause is that the sum_range and a criteria_range are different sizes. Every range in SUMIFS must have the same number of rows and columns. Make them identical (for example all A2:A100) and the error clears.
Does SUMIFS work the same in Google Sheets?
Yes. SUMIFS has identical syntax in Microsoft Excel and Google Sheets. In Apple Numbers it is also supported with the same arguments.
Related guides
- SUMIF & SUMIFS Guide — the single-condition basics.
- IF Formula Guide — conditional logic.
- Fix the #REF! Error — when references break.
- FAQ — more about FormulaAI.