formulastreak

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.

Leaf & Loam orders · edit K1 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultK1 = 15
ABCDEFGHIJK
1OrderDateCustomerProductCategoryQtyPriceStatusPlants qty15
210012 Mar 2026Mara OkaforMonsteraPlants1$34.00Shipped
310022 Mar 2026Jonas LindTerracotta potPots3$12.50Shipped
410033 Mar 2026Priya RamanSnake plantPlants2$22.00Shipped
510044 Mar 2026Tomás RuizWatering canTools1$18.00Pending
610055 Mar 2026Elif DemirFernPlants4$9.50Shipped
710065 Mar 2026Mara OkaforPotting mixSoil2$7.25Returned
810076 Mar 2026Kenji MoriPruning shearsTools1$24.00Shipped
910087 Mar 2026Ines VargaCeramic potPots2$19.00Pending
1010098 Mar 2026Jonas LindMonsteraPlants1$34.00Shipped
1110109 Mar 2026Sofia RossiFernPlants6$9.50Shipped
12101110 Mar 2026Priya RamanPotting mixSoil5$7.25Shipped
13101211 Mar 2026Leo BrandtSnake plantPlants1$22.00Pending

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.

Leaf & Loam orders · edit K1 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultK1 = 14
ABCDEFGHIJK
1OrderDateCustomerProductCategoryQtyPriceStatusPlants shipped14
210012 Mar 2026Mara OkaforMonsteraPlants1$34.00Shipped
310022 Mar 2026Jonas LindTerracotta potPots3$12.50Shipped
410033 Mar 2026Priya RamanSnake plantPlants2$22.00Shipped
510044 Mar 2026Tomás RuizWatering canTools1$18.00Pending
610055 Mar 2026Elif DemirFernPlants4$9.50Shipped
710065 Mar 2026Mara OkaforPotting mixSoil2$7.25Returned
810076 Mar 2026Kenji MoriPruning shearsTools1$24.00Shipped
910087 Mar 2026Ines VargaCeramic potPots2$19.00Pending
1010098 Mar 2026Jonas LindMonsteraPlants1$34.00Shipped
1110109 Mar 2026Sofia RossiFernPlants6$9.50Shipped
12101110 Mar 2026Priya RamanPotting mixSoil5$7.25Shipped
13101211 Mar 2026Leo BrandtSnake plantPlants1$22.00Pending

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.

Leaf & Loam orders · edit K2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultK2 = 13
ABCDEFGHIJK
1OrderDateCustomerProductCategoryQtyPriceStatusFrom8 Mar 2026
210012 Mar 2026Mara OkaforMonsteraPlants1$34.00ShippedQty since13
310022 Mar 2026Jonas LindTerracotta potPots3$12.50Shipped
410033 Mar 2026Priya RamanSnake plantPlants2$22.00Shipped
510044 Mar 2026Tomás RuizWatering canTools1$18.00Pending
610055 Mar 2026Elif DemirFernPlants4$9.50Shipped
710065 Mar 2026Mara OkaforPotting mixSoil2$7.25Returned
810076 Mar 2026Kenji MoriPruning shearsTools1$24.00Shipped
910087 Mar 2026Ines VargaCeramic potPots2$19.00Pending
1010098 Mar 2026Jonas LindMonsteraPlants1$34.00Shipped
1110109 Mar 2026Sofia RossiFernPlants6$9.50Shipped
12101110 Mar 2026Priya RamanPotting mixSoil5$7.25Shipped
13101211 Mar 2026Leo BrandtSnake plantPlants1$22.00Pending

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).

Q3 sales team · edit I1 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultI1 = 1
ABCDEFGHI
1RepRegionJulAugSepTargetNorth > 12k1
2Amara OseiNorth$12,400$13,100$11,800$36,000
3Ben FischerSouth$9,800$10,250$12,100$30,000
4Lina ChenEast$15,200$14,800$16,900$45,000
5Diego AlvarezWest$8,700$9,100$8,400$28,000
6Eva NovakNorth$11,200$12,600$13,900$36,000
7Farid HaddadSouth$13,300$12,900$14,100$39,000
8Grace KimEast$10,100$9,600$10,800$33,000
9Hugo MartinWest$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 ~*.

Leaf & Loam orders · edit K1 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultK1 = 12
ABCDEFGHIJK
1OrderDateCustomerProductCategoryQtyPriceStatusPots qty12
210012 Mar 2026Mara OkaforMonsteraPlants1$34.00Shipped
310022 Mar 2026Jonas LindTerracotta potPots3$12.50Shipped
410033 Mar 2026Priya RamanSnake plantPlants2$22.00Shipped
510044 Mar 2026Tomás RuizWatering canTools1$18.00Pending
610055 Mar 2026Elif DemirFernPlants4$9.50Shipped
710065 Mar 2026Mara OkaforPotting mixSoil2$7.25Returned
810076 Mar 2026Kenji MoriPruning shearsTools1$24.00Shipped
910087 Mar 2026Ines VargaCeramic potPots2$19.00Pending
1010098 Mar 2026Jonas LindMonsteraPlants1$34.00Shipped
1110109 Mar 2026Sofia RossiFernPlants6$9.50Shipped
12101110 Mar 2026Priya RamanPotting mixSoil5$7.25Shipped
13101211 Mar 2026Leo BrandtSnake plantPlants1$22.00Pending

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
Select K2 and type = to start its formula (in the cell or the formula bar)
ABCDEFGHIJK
1OrderDateCustomerProductCategoryQtyPriceStatusTotal
210012 Mar 2026Mara OkaforMonsteraPlants1$34.00Shipped$34.00Plants shipped
310022 Mar 2026Jonas LindTerracotta potPots3$12.50Shipped$37.50
410033 Mar 2026Priya RamanSnake plantPlants2$22.00Shipped$44.00
510044 Mar 2026Tomás RuizWatering canTools1$18.00Pending$18.00
610055 Mar 2026Elif DemirFernPlants4$9.50Shipped$38.00
710065 Mar 2026Mara OkaforPotting mixSoil2$7.25Returned$14.50
810076 Mar 2026Kenji MoriPruning shearsTools1$24.00Shipped$24.00
910087 Mar 2026Ines VargaCeramic potPots2$19.00Pending$38.00
1010098 Mar 2026Jonas LindMonsteraPlants1$34.00Shipped$34.00
1110109 Mar 2026Sofia RossiFernPlants6$9.50Shipped$57.00
12101110 Mar 2026Priya RamanPotting mixSoil5$7.25Shipped$36.25
13101211 Mar 2026Leo BrandtSnake plantPlants1$22.00Pending$22.00

Common mistakes

  • Mixing up the argument order between SUMIF (sum range last) and SUMIFS (sum range first).

  • Writing >=K1 in 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.

Practise it now