Syntax
SUMIFS(sum_range, criteria_range1, criteria1, ...)
| Parameter | Description |
|---|---|
| sum_range | Parameter of the SUMIFS function. |
| criteria_range1 | Parameter of the SUMIFS function. |
| criteria1 | Parameter of the SUMIFS function. |
| ... | Parameter of the SUMIFS function. |
Examples
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Product | Amount | Date |
| 2 | East | Widget | 1200 | 2024-01-15 |
| 3 | West | Gadget | 800 | 2024-02-20 |
| 4 | East | Widget | 2100 | 2024-03-05 |
| 5 | East | Gadget | 450 | 2024-01-28 |
Sum sales by region and product
Formula
=SUMIFS(C2:C100, A2:A100, "East", B2:B100, "Widget")
Returns: 18,500 — total sales where region is "East" AND product is "Widget"
Sum within a date range
Formula
=SUMIFS(C2:C100, D2:D100, ">="&DATE(2024,1,1), D2:D100, "<"&DATE(2024,4,1))
Returns: 125,000 — total sales for Q1 2024
Sum with amount threshold
Formula
=SUMIFS(C2:C100, A2:A100, "East", C2:C100, ">500")
Returns: 42,000 — sums East region sales but only orders above $500
Common Errors
#VALUE!
The criteria ranges and sum range have different sizes. All ranges must span the same number of rows.
Returns 0
No rows match all criteria. Test individual criteria with SUMIF to isolate the issue.
Tips
Sum range comes first
Unlike SUMIF (range, criteria, sum_range), SUMIFS puts the sum range first: SUMIFS(sum_range, criteria_range1, criteria1, ...).
Dynamic criteria with cells
=SUMIFS(C:C, A:A, F1, B:B, G1) pulls criteria from cells F1 and G1 for flexible reporting.
SUMIFS vs SUMPRODUCT
SUMIFS is faster and simpler for standard multi-criteria sums. Use SUMPRODUCT only when you need calculated criteria or OR logic.
Try SUMIFS in Viztab
Import your data and use SUMIFS with 370+ other formulas. No signup required.
Open Viztab