SUMIF and COUNTIF practice problems
SUMIF, COUNTIF and SUMIFS are behind almost every report: total sales for one region, the number of orders over $1,000, revenue for one product in one region. These exercises use a real orders table so you can see exactly which rows match.
Try it yourself
A live spreadsheet. Type a formula and press Enter to check it.
What was the total revenue from the North region?
Write your formula in the green cell, I2.
Click a cell to add it to your formula, drag to select a range, or click a column letter for the whole column.
All 11 conditional math exercises
- 1→
Revenue for one region
FreeWhat was the total revenue from the North region?
- 2→
Count one product
FreeHow many orders were for Laptops?
- 3→
Revenue for one rep
PremiumWhat was the total revenue for Maya?
- 4→
Orders over $1,000
PremiumHow many orders had revenue over $1,000?
- 5→
Two conditions at once
PremiumWhat was the revenue from Monitors sold in the South region?
- 6→
COUNTIFS with a number
PremiumHow many Laptop orders had 3 or more units?
- 7→
Average for one rep
PremiumOn average, how many units did Sam sell per order?
- 8→
Wildcards
PremiumWhat was the total revenue from reps whose name starts with S?
- 9→
Smallest matching value
PremiumWhat was the revenue of the smallest Laptop order?
- 10→
Revenue for one month
PremiumWhat was the total revenue in March 2026?
- 11→
AVERAGEIFS
PremiumWhat was the average revenue of North orders with more than 2 units?
The conditional math functions, with examples
Every example runs on q1-orders.xlsx, the same sheet the exercises use, and the results are calculated from that data.
SUMIF
Full guide →Adds the values in one range for the rows where another range matches a condition.
SUMIF(range, criteria, [sum_range])Example
=SUMIF(B2:B11, "North", F2:F11)returns$8,230.00COUNTIF
Full guide →Counts the cells that match a condition, like a product name or a number above a limit.
COUNTIF(range, criteria)Example
=COUNTIF(D2:D11, "Laptop")returns4SUMIFS
Full guide →SUMIF with as many conditions as you need. All of them must be true for a row to count.
SUMIFS(sum_range, criteria_range1, criteria1, …)Example
=SUMIFS(F2:F11, B2:B11, "North", D2:D11, "Laptop")returns$6,000.00AVERAGEIF
Averages only the values whose row matches a condition.
AVERAGEIF(range, criteria, [average_range])Example
=AVERAGEIF(B2:B11, "East", E2:E11)returns9
Common mistakes
- 1
Putting the sum range in the wrong place
SUMIF ends with the cells to add:
SUMIF(range, criteria, sum_range). SUMIFS starts with them:SUMIFS(sum_range, criteria_range, criteria, …). Mixing the two orders up is the most common SUMIFS error. - 2
Writing a comparison outside the quotes
The operator goes inside the quotes:
">1000". To compare with a cell, join the operator to it:">"&H1. Writing">H1"compares against the text "H1". - 3
Ranges of different sizes
In SUMIFS, every criteria range must have the same number of rows as the sum range, or the formula returns #VALUE!. Select whole matching columns of the same table.