SUMIF practice exercises
Add up only the rows that meet a condition, with the condition in the same column or in another one.
How SUMIF works
SUMIF adds up the numbers in the rows that meet one condition. It takes three arguments: the range to test, the condition, and the range to add up.
=SUMIF($B$2:$B$100,"North",$D$2:$D$100) adds the sales in column D for every row where the region in column B is North. Put the condition in a cell instead, as in =SUMIF($B$2:$B$100,F2,$D$2:$D$100), and you can copy the formula down a list of regions to build a summary table.
Conditions can be text, a number, a comparison in quotes such as ">500", or a wildcard such as "*desk*" for any text that contains desk. With two or more conditions, use SUMIFS. The exercises below cover text and number conditions, conditions from a cell, wildcards, and summing one column while testing another.
- 1SUMIF with a text criterionIn D2, use SUMIF to total the Units sold of Apple.
- 2SUMIF with a number conditionPremiumIn D2, use SUMIF to total the amounts greater than 100.
- 3SUMIF with a separate sum_rangePremiumIn E2, use SUMIF to total the Amount of orders with more than 3 units.
- 4SUMIF with the criterion in a cellPremiumIn E2, total the Sales of the rep named in D2. Point SUMIF at D2 instead of typing the name, so the total follows when D2 changes.
- 5SUMIF with a wildcardPremiumIn E1, total the sales of every product with Chair anywhere in its name. Use * on both sides of the word: SUMIF(A2:A8,"*Chair*",B2:B8).
Functions in this topic
Read the guide
- Excel test for a job interview: what it looks like and how to practice
- Free Excel course online: learn Excel from your first formula to job-ready
- Pivot tables explained, and the formulas that do the same job
- Excel for finance and accounting: the skills you actually use
- Excel budget template: free download and the formulas behind it
- Excel inventory data: free practice file with tasks and answers
- Excel customer data: free practice file with tasks and answers
- Excel finance data: free practice file with tasks and answers
- Best Excel courses online in 2026: 10 places to learn Excel, ranked
- Udemy vs Coursera vs LinkedIn Learning for Excel: which to pick
Questions
How do I use SUMIF with multiple criteria?
Use SUMIFS instead. It takes the sum range first, then a range and a criterion for each condition, as in =SUMIFS(D2:D10,B2:B10,"North",C2:C10,">100"). Every condition must be true for a row to count. If you want rows that match one condition or the other, add two SUMIF formulas together.
How do I use SUMIF with dates?
Put the comparison in quotes and join the date with &, as in =SUMIF(A2:A10,">="&F1,B2:B10), which adds the amounts dated on or after the date in F1. SUMIF can test only one limit. To sum between two dates, use SUMIFS with the date column twice.
Why does SUMIF return 0?
Usually no row matches the criterion. Check for extra spaces and typos in the criterion. If the numbers in the sum range are stored as text, SUMIF skips them and you also get 0. Numbers that sit at the left edge of a cell are often text.