What it does
SUMIF totals values that meet one condition.
Syntax or pattern
=SUMIF(range, criteria, [sum_range])✅
5 practical examples
1
Total sales for one region
Add sales where the region is East.
=SUMIF(Sales[Region],"East",Sales[Amount])The criteria range stores regions; the sum range stores amounts.
2
Total values above a threshold
Sum orders over 100.
=SUMIF(B2:B100,">100")When no sum range is supplied, Excel sums the criteria range.
3
Total by selected category
Use a cell as the criteria.
=SUMIF(Products[Category],E2,Products[Sales])This makes the formula reusable.
4
Total text matches
Sum sales for a product.
=SUMIF(Sales[Product],"Coffee",Sales[Amount])Text criteria must be in quotes.
5
Total blanks or non-blanks
Sum rows with missing owners.
=SUMIF(Tasks[Owner],"",Tasks[Hours])Blank criteria can reveal incomplete data.
Common mistakes to avoid
- Selecting ranges with different sizes.
- Using text values where numbers are required.
- Forgetting how blanks and hidden rows are treated.
Related Excel examples
FAQ
Can this formula use Excel Tables?
Yes. Structured references often make summary formulas easier to read.