What it does
SUMIF totals values that meet one condition.
Syntax or pattern
=SUMIF(range, criteria, [sum_range])5 practical examples
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.
Total values above a threshold
Sum orders over 100.
=SUMIF(B2:B100,">100")When no sum range is supplied, Excel sums the criteria range.
Total by selected category
Use a cell as the criteria.
=SUMIF(Products[Category],E2,Products[Sales])This makes the formula reusable.
Total text matches
Sum sales for a product.
=SUMIF(Sales[Product],"Coffee",Sales[Amount])Text criteria must be in quotes.
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.
Here are some ideas for you
Optional resources that may help if you are learning formulas, building reports, or working in spreadsheets often.
- Excel formula booksSee ideas
Practice formulas with structured examples you can keep beside your desk.
- Excel shortcut guidesSee ideas
Build speed with keyboard shortcuts for selection, formatting and navigation.
- Numeric keypadsSee ideas
Helpful if you enter many numbers on a laptop or compact keyboard.
- External monitorsSee ideas
Useful for viewing large worksheets, formulas and reference tables side by side.
- Desk notebooksSee ideas
Sketch formula logic, report ideas and table structures before building.
- Laptop standsSee ideas
Make long spreadsheet sessions more comfortable and ergonomic.
Some links in this section may be affiliate links. Choose only what is useful for your own work.