Total East revenue
What is the revenue for every East row?
Formula: =SUMIF(A2:A6,"East",D2:D6) → 980
Hands-on Excel exercises
SUMIFS Exercises
Practice SUMIFS in a live spreadsheet. Build single- and multi-criteria totals with dates, text, and comparisons, plus worked answers.
SUMIFS answers a common spreadsheet question: what is the total for rows that meet all of these conditions? The live example sums units for one region and one product. Change either criterion, widen a range, or replace a text condition with a comparison to see how the result responds. The order matters: SUMIFS starts with the values to add, then takes pairs of criteria ranges and criteria.
The linked exercises progress from COUNTIF and SUMIF to multi-condition totals, counts, averages, and a dashboard month total. Real mistakes often come from range shape rather than function choice. Every criteria range must cover the same rows as the sum range. Text criteria need quotes, and operators such as greater than must be joined to values correctly.
The worked answers show a repeatable checking method: filter the source table to the same conditions, compare a small manual subtotal, and test one criterion at a time. That makes a zero result informative rather than mysterious. When the formula is stable, lock the source ranges and leave report criteria relative only where copying the formula should change them.
Your turn
Your turn: in G2, total East Lamp units using the criteria in E2 and F2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Units | Revenue | Region | Product | |
| 2 | East | Lamp | 12 | 360 | East | Lamp | |
| 3 | East | Desk | 5 | 500 | |||
| 4 | West | Lamp | 8 | 240 | |||
| 5 | East | Lamp | 4 | 120 | |||
| 6 | West | Chair | 6 | 270 |
Not yet solved.
Answer: =SUMIFS(C2:C6,A2:A6,E2,B2:B6,F2)
Related exercises
Open a task in its lesson.
- In E2:E4, count how many deals each manager has.
- In F2:F4, calculate each manager's revenue.
- In E6, count how many deals are worth more than $800.
- In F2, calculate the total of Emma's deals in Boston.
- In F3, count how many deals in Chicago are worth more than $1,000.
- In F4, find the average deal amount in Denver.
- In F2:F9, pull each category's markup from the lookup table K2:L4.
- In F3, calculate the revenue for the month chosen in F1 and format the cell as currency.
Worked answers
Try the spreadsheet first, then compare your formula and result.
Total Lamp revenue
What is the revenue for Lamp across all regions?
Formula: =SUMIF(B2:B6,"Lamp",D2:D6) → 720
Frequently asked questions
What is the difference between SUMIF and SUMIFS?
SUMIF handles one condition. SUMIFS supports multiple conditions and places the sum range first.
Why does SUMIFS return zero?
Check that ranges align, criteria text matches the source, numbers are stored as numbers, and date criteria use valid dates.
Does SUMIFS use AND or OR logic?
Criteria pairs in one SUMIFS use AND logic. For OR logic, add separate SUMIFS results or use another suitable pattern.