Introduction
The Excel SUMIF and SUMIFS functions are powerful tools for conditional summing. Instead of adding all numbers blindly, they let you apply rules, such as summing revenue by region, customer, or product category. This makes them essential for data analysis, financial modeling, and reporting.
In this guide, you’ll:
- Learn the syntax of SUMIF and SUMIFS
- Practice with 20 real-world problems and solutions
- Download a free Excel practice file
Download the Practice File
This file contains:
✔ Dataset (Customer, Region, Product, Revenue, Month)
✔ 20 step-by-step problems
✔ Ready-made solutions
Excel SUMIF & SUMIFS Syntax
SUMIF (one condition):
=SUMIF(range, criteria, sum_range)- range → Range to evaluate (e.g., Region column)
- criteria → Condition (e.g., “North”)
- sum_range → Values to sum (optional)
Example:
=SUMIF(A2:A20,"North",B2:B20)
SUMIFS (multiple conditions):
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …)Example:
=SUMIFS(C2:C20,A2:A20,"North",B2:B20,"Electronics")Adds sales for Electronics in the North region.
Example Dataset (Practice File)
| Customer | Region | Product | Revenue | Month |
|---|---|---|---|---|
| John | North | A | 500 | Jan |
| Mary | South | B | 700 | Jan |
| Sam | East | A | 300 | Feb |
| Rita | West | C | 400 | Mar |
| Alex | North | B | 600 | Feb |
| Nina | South | A | 450 | Mar |
| Tom | East | C | 800 | Jan |
| Sara | West | B | 550 | Feb |
| Leo | North | A | 650 | Mar |
| Mia | South | C | 720 | Jan |
20 Excel SUMIF & SUMIFS Problems (With Solutions)
- Total revenue for North region
=SUMIF(B2:B11, "North", D2:D11)→ 500 + 600 + 650 = 1750 - Total sales for Product A
=SUMIF(C2:C11, "A", D2:D11)→ 500 + 300 + 450 + 650 = 1900 - Revenue greater than 500
=SUMIF(D2:D11, ">500")→ 700 + 600 + 800 + 550 + 650 + 720 = 4520 - Revenue in January only
=SUMIF(E2:E11, "Jan", D2:D11)→ 500 + 700 + 800 + 720 = 2720 - Total revenue for South region, Product B
=SUMIFS(D2:D11, B2:B11, "South", C2:C11, "B")→ 700 - Revenue from North region in February
=SUMIFS(D2:D11, B2:B11, "North", E2:E11, "Feb")→ 600 - Revenue from Product C in March
=SUMIFS(D2:D11, C2:C11, "C", E2:E11, "Mar")→ 400 - Total revenue excluding Product A
=SUMIF(C2:C11, "<>A", D2:D11)→ 700 + 600 + 400 + 550 + 720 = 2970 - Revenue for Alex only
=SUMIF(A2:A11, "Alex", D2:D11)→ 600 - Revenue for East & West regions
=SUM(SUMIF(B2:B11, {"East","West"}, D2:D11))→ 300 + 800 + 400 + 550 = 2050 - Revenue for customers with sales < 500
=SUMIF(D2:D11, "<500")→ 300 + 400 + 450 = 1150 - Revenue for Feb & Mar combined
=SUM(SUMIF(E2:E11, {"Feb","Mar"}, D2:D11))→ 300 + 600 + 450 + 550 + 650 + 400 = 2950 - Revenue from North region excluding Product B
=SUMIFS(D2:D11, B2:B11, "North", C2:C11, "<>B")→ 500 + 650 = 1150 - Revenue for John in Jan
=SUMIFS(D2:D11, A2:A11, "John", E2:E11, "Jan")→ 500 - Revenue for Product A above 400
=SUMIFS(D2:D11, C2:C11, "A", D2:D11, ">400")→ 500 + 450 + 650 = 1600 - Revenue for South region in Q1 (Jan–Mar)
=SUMIFS(D2:D11, B2:B11, "South", E2:E11, {"Jan","Feb","Mar"})→ 700 + 450 + 720 = 1870 - Revenue for multiple products (A & B)
=SUM(SUMIF(C2:C11, {"A","B"}, D2:D11))→ 500 + 300 + 450 + 650 + 700 + 600 + 550 = 3750 - Revenue for regions starting with ‘N’
=SUMIF(B2:B11, "N*", D2:D11)→ 500 + 600 + 650 = 1750 - Revenue from Product A in Feb and March only
=SUMIFS(D2:D11, C2:C11, "A", E2:E11, {"Feb","Mar"})→ 300 + 450 + 650 = 1400 - Revenue from all customers except John
=SUMIF(A2:A11, "<>John", D2:D11)→ 700 + 300 + 400 + 600 + 450 + 800 + 550 + 650 + 720 = 5170
Final Thoughts
SUMIF and SUMIFS are powerful Excel tools for adding numbers based on one or more conditions. By practicing these 20 examples, you can quickly analyze your data and make better decisions.
Free Excel file Download here to try all 20 problems and solutions yourself.
