SUMIFS: totals with conditions
Updated · Works in: all current Excel versions, Google Sheets · Every example checked before release
Short answer
=SUMIFS(sum_range, range1, criteria1, range2, criteria2, …) adds the rows that match every condition. Criteria can be text, numbers, comparisons in quotes like ">=100", a cell joined with & (">="&K1) or wildcards like "*pot*".
SUMIFS answers questions like “how much did we sell in the North?” or “what did shipped plant orders bring in?” without filtering or copying anything. The examples use a small plant shop's March orders.
SUMIF: one condition
SUMIF(range, criteria, sum_range) checks each row of range against the criteria and adds the matching rows of sum_range. Text matching ignores case.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Customer | Product | Category | Qty | Price | Status | Plants qty | 15 | |
| 2 | 1001 | 2 Mar 2026 | Mara Okafor | Monstera | Plants | 1 | $34.00 | Shipped | |||
| 3 | 1002 | 2 Mar 2026 | Jonas Lind | Terracotta pot | Pots | 3 | $12.50 | Shipped | |||
| 4 | 1003 | 3 Mar 2026 | Priya Raman | Snake plant | Plants | 2 | $22.00 | Shipped | |||
| 5 | 1004 | 4 Mar 2026 | Tomás Ruiz | Watering can | Tools | 1 | $18.00 | Pending | |||
| 6 | 1005 | 5 Mar 2026 | Elif Demir | Fern | Plants | 4 | $9.50 | Shipped | |||
| 7 | 1006 | 5 Mar 2026 | Mara Okafor | Potting mix | Soil | 2 | $7.25 | Returned | |||
| 8 | 1007 | 6 Mar 2026 | Kenji Mori | Pruning shears | Tools | 1 | $24.00 | Shipped | |||
| 9 | 1008 | 7 Mar 2026 | Ines Varga | Ceramic pot | Pots | 2 | $19.00 | Pending | |||
| 10 | 1009 | 8 Mar 2026 | Jonas Lind | Monstera | Plants | 1 | $34.00 | Shipped | |||
| 11 | 1010 | 9 Mar 2026 | Sofia Rossi | Fern | Plants | 6 | $9.50 | Shipped | |||
| 12 | 1011 | 10 Mar 2026 | Priya Raman | Potting mix | Soil | 5 | $7.25 | Shipped | |||
| 13 | 1012 | 11 Mar 2026 | Leo Brandt | Snake plant | Plants | 1 | $22.00 | Pending |
SUMIFS: several conditions
SUMIFS(sum_range, range1, criteria1, range2, criteria2, …) adds rows that match every condition. Watch the order: in SUMIFS the range to add comes first, while in SUMIF it comes last.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Customer | Product | Category | Qty | Price | Status | Plants shipped | 14 | |
| 2 | 1001 | 2 Mar 2026 | Mara Okafor | Monstera | Plants | 1 | $34.00 | Shipped | |||
| 3 | 1002 | 2 Mar 2026 | Jonas Lind | Terracotta pot | Pots | 3 | $12.50 | Shipped | |||
| 4 | 1003 | 3 Mar 2026 | Priya Raman | Snake plant | Plants | 2 | $22.00 | Shipped | |||
| 5 | 1004 | 4 Mar 2026 | Tomás Ruiz | Watering can | Tools | 1 | $18.00 | Pending | |||
| 6 | 1005 | 5 Mar 2026 | Elif Demir | Fern | Plants | 4 | $9.50 | Shipped | |||
| 7 | 1006 | 5 Mar 2026 | Mara Okafor | Potting mix | Soil | 2 | $7.25 | Returned | |||
| 8 | 1007 | 6 Mar 2026 | Kenji Mori | Pruning shears | Tools | 1 | $24.00 | Shipped | |||
| 9 | 1008 | 7 Mar 2026 | Ines Varga | Ceramic pot | Pots | 2 | $19.00 | Pending | |||
| 10 | 1009 | 8 Mar 2026 | Jonas Lind | Monstera | Plants | 1 | $34.00 | Shipped | |||
| 11 | 1010 | 9 Mar 2026 | Sofia Rossi | Fern | Plants | 6 | $9.50 | Shipped | |||
| 12 | 1011 | 10 Mar 2026 | Priya Raman | Potting mix | Soil | 5 | $7.25 | Shipped | |||
| 13 | 1012 | 11 Mar 2026 | Leo Brandt | Snake plant | Plants | 1 | $22.00 | Pending |
Comparisons and criteria from a cell
Put comparison operators inside the quotes: ">=3", "<>Returned". To compare with a cell, join the operator to it with &: ">="&K1. Now changing K1 updates the total, which is great for dates.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Customer | Product | Category | Qty | Price | Status | From | 8 Mar 2026 | |
| 2 | 1001 | 2 Mar 2026 | Mara Okafor | Monstera | Plants | 1 | $34.00 | Shipped | Qty since | 13 | |
| 3 | 1002 | 2 Mar 2026 | Jonas Lind | Terracotta pot | Pots | 3 | $12.50 | Shipped | |||
| 4 | 1003 | 3 Mar 2026 | Priya Raman | Snake plant | Plants | 2 | $22.00 | Shipped | |||
| 5 | 1004 | 4 Mar 2026 | Tomás Ruiz | Watering can | Tools | 1 | $18.00 | Pending | |||
| 6 | 1005 | 5 Mar 2026 | Elif Demir | Fern | Plants | 4 | $9.50 | Shipped | |||
| 7 | 1006 | 5 Mar 2026 | Mara Okafor | Potting mix | Soil | 2 | $7.25 | Returned | |||
| 8 | 1007 | 6 Mar 2026 | Kenji Mori | Pruning shears | Tools | 1 | $24.00 | Shipped | |||
| 9 | 1008 | 7 Mar 2026 | Ines Varga | Ceramic pot | Pots | 2 | $19.00 | Pending | |||
| 10 | 1009 | 8 Mar 2026 | Jonas Lind | Monstera | Plants | 1 | $34.00 | Shipped | |||
| 11 | 1010 | 9 Mar 2026 | Sofia Rossi | Fern | Plants | 6 | $9.50 | Shipped | |||
| 12 | 1011 | 10 Mar 2026 | Priya Raman | Potting mix | Soil | 5 | $7.25 | Shipped | |||
| 13 | 1012 | 11 Mar 2026 | Leo Brandt | Snake plant | Plants | 1 | $22.00 | Pending |
COUNTIFS, AVERAGEIFS and MAXIFS
The same criteria work for counting and averaging. COUNTIF(range, criteria) counts matching cells; COUNTIFS(range1, criteria1, range2, criteria2, …) counts rows that match every pair; AVERAGEIFS(average_range, range1, criteria1, …) averages the matching rows; MAXIFS(max_range, range1, criteria1, …) and MINIFS find the largest and smallest matching value (Excel 2019 or later and Google Sheets).
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Jul | Aug | Sep | Target | North > 12k | 1 | |
| 2 | Amara Osei | North | $12,400 | $13,100 | $11,800 | $36,000 | |||
| 3 | Ben Fischer | South | $9,800 | $10,250 | $12,100 | $30,000 | |||
| 4 | Lina Chen | East | $15,200 | $14,800 | $16,900 | $45,000 | |||
| 5 | Diego Alvarez | West | $8,700 | $9,100 | $8,400 | $28,000 | |||
| 6 | Eva Novak | North | $11,200 | $12,600 | $13,900 | $36,000 | |||
| 7 | Farid Haddad | South | $13,300 | $12,900 | $14,100 | $39,000 | |||
| 8 | Grace Kim | East | $10,100 | $9,600 | $10,800 | $33,000 | |||
| 9 | Hugo Martin | West | $14,500 | $15,800 | $15,100 | $42,000 |
Wildcards
In text criteria, * matches any run of characters and ? exactly one. "*pot*" matches “Terracotta pot” and “Ceramic pot”. To match a literal asterisk, write ~*.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Customer | Product | Category | Qty | Price | Status | Pots qty | 12 | |
| 2 | 1001 | 2 Mar 2026 | Mara Okafor | Monstera | Plants | 1 | $34.00 | Shipped | |||
| 3 | 1002 | 2 Mar 2026 | Jonas Lind | Terracotta pot | Pots | 3 | $12.50 | Shipped | |||
| 4 | 1003 | 3 Mar 2026 | Priya Raman | Snake plant | Plants | 2 | $22.00 | Shipped | |||
| 5 | 1004 | 4 Mar 2026 | Tomás Ruiz | Watering can | Tools | 1 | $18.00 | Pending | |||
| 6 | 1005 | 5 Mar 2026 | Elif Demir | Fern | Plants | 4 | $9.50 | Shipped | |||
| 7 | 1006 | 5 Mar 2026 | Mara Okafor | Potting mix | Soil | 2 | $7.25 | Returned | |||
| 8 | 1007 | 6 Mar 2026 | Kenji Mori | Pruning shears | Tools | 1 | $24.00 | Shipped | |||
| 9 | 1008 | 7 Mar 2026 | Ines Varga | Ceramic pot | Pots | 2 | $19.00 | Pending | |||
| 10 | 1009 | 8 Mar 2026 | Jonas Lind | Monstera | Plants | 1 | $34.00 | Shipped | |||
| 11 | 1010 | 9 Mar 2026 | Sofia Rossi | Fern | Plants | 6 | $9.50 | Shipped | |||
| 12 | 1011 | 10 Mar 2026 | Priya Raman | Potting mix | Soil | 5 | $7.25 | Shipped | |||
| 13 | 1012 | 11 Mar 2026 | Leo Brandt | Snake plant | Plants | 1 | $22.00 | Pending |
Check yourself
One real exercise from the “Conditional totals” unit. Checked on our server; no account needed.Now two conditions: revenue (Total, column I) from orders whose Category (E) is Plants and whose Status (H) is Shipped. Answer in K2.
- Use SUMIFS
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Customer | Product | Category | Qty | Price | Status | Total | ||
| 2 | 1001 | 2 Mar 2026 | Mara Okafor | Monstera | Plants | 1 | $34.00 | Shipped | $34.00 | Plants shipped | |
| 3 | 1002 | 2 Mar 2026 | Jonas Lind | Terracotta pot | Pots | 3 | $12.50 | Shipped | $37.50 | ||
| 4 | 1003 | 3 Mar 2026 | Priya Raman | Snake plant | Plants | 2 | $22.00 | Shipped | $44.00 | ||
| 5 | 1004 | 4 Mar 2026 | Tomás Ruiz | Watering can | Tools | 1 | $18.00 | Pending | $18.00 | ||
| 6 | 1005 | 5 Mar 2026 | Elif Demir | Fern | Plants | 4 | $9.50 | Shipped | $38.00 | ||
| 7 | 1006 | 5 Mar 2026 | Mara Okafor | Potting mix | Soil | 2 | $7.25 | Returned | $14.50 | ||
| 8 | 1007 | 6 Mar 2026 | Kenji Mori | Pruning shears | Tools | 1 | $24.00 | Shipped | $24.00 | ||
| 9 | 1008 | 7 Mar 2026 | Ines Varga | Ceramic pot | Pots | 2 | $19.00 | Pending | $38.00 | ||
| 10 | 1009 | 8 Mar 2026 | Jonas Lind | Monstera | Plants | 1 | $34.00 | Shipped | $34.00 | ||
| 11 | 1010 | 9 Mar 2026 | Sofia Rossi | Fern | Plants | 6 | $9.50 | Shipped | $57.00 | ||
| 12 | 1011 | 10 Mar 2026 | Priya Raman | Potting mix | Soil | 5 | $7.25 | Shipped | $36.25 | ||
| 13 | 1012 | 11 Mar 2026 | Leo Brandt | Snake plant | Plants | 1 | $22.00 | Pending | $22.00 |
Common mistakes
Mixing up the argument order between SUMIF (sum range last) and SUMIFS (sum range first).
Writing
>=K1in quotes, which compares against the text “K1”. Use">="&K1.Using ranges of different sizes. SUMIFS needs every range to be the same shape.
Expecting OR logic. SUMIFS combines conditions with AND; for OR, add two SUMIFS together.
Questions
Can SUMIFS use OR conditions?+
Not directly: every condition must match. Add separate SUMIFS together, e.g. `=SUMIFS(F:F,H:H,"Pending")+SUMIFS(F:F,H:H,"Returned")`.
Does SUMIFS work in Google Sheets?+
Yes. Google Sheets has SUMIFS, SUMIF, COUNTIFS and AVERAGEIFS, with the sum range first in SUMIFS just like Excel.
Reading is step one. Now make it stick.
Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.