BeginnerMath Functions
Total sales for one region with SUMIF
Add up only the rows matching a text condition.
SUMIF
6 min15 points
SUMIF
Adds up only the values that meet one condition.
Syntax
SUMIF(range, criteria, [sum_range])
Math Functions.
Practise SUMIF7 interactive exercises, free, no account needed
A worked example
=SUMIF(D2:D9,"West",C2:C9)
Returns 3,815
Adds the values in column C for rows where column D says West.
Where it goes wrong
The argument order trips people up: the range you search comes first, the range you add comes last. With only two arguments it adds up the range it searched.
What the exercises catch
Each of these is a wrong turn the practice exercises recognise and name. They are the misconceptions, not the answers.
- Your sum range is one row shorter than your criteria range. Excel lines them up from the top, so the last row is silently dropped. Both ranges must be the same size.Total sales for one region with SUMIF
- 1510 is today's average. Add one order and the threshold moves, but this formula keeps using the old number and quietly reports the wrong total. Work the average out inside the formula.Total only the orders above average
- That averages every region together and gives 750, which is not what any single region did. The condition has to be applied before the averaging.Average one region without AVERAGEIF
- This is the belief that produces the bug in the scenario. SUMIF does put the sum range last — SUMIFS puts it first, which is a genuine inconsistency in Excel rather than something you misremembered.Which range does SUMIFS want first?
Practise SUMIF
Knowing what a function does and being able to write it from memory are different skills. These close the gap.
All SUMIF practice exercises →
- IntermediateData Analysis
Total only the orders above average
Sum the orders that beat the average, without typing the average in yourself.
SUMIF · AVERAGE
8 min25 points - IntermediateData Analysis
Average one region without AVERAGEIF
Build a conditional average out of a conditional sum and a conditional count.
SUMIF · COUNTIF
9 min30 points - AdvancedData Analysis
Build a regional summary from raw orders
Produce a summary line combining count, total and average for one region.
COUNTIF · SUMIF
16 min40 points