Step 1 of 10
SUMIF, COUNTIF and IF exercises with answers
In this lesson you practise SUMIF, COUNTIF and IF on small work sheets: you count tickets, total sales by region and sort orders into bands, and each exercise is checked as you go, with the answers in hints and worked solutions. Every formula is animated row by row, so you see which rows pass the test and which numbers get added.
Watch the sheet below answer three questions about eight sales orders. The orange cells are the ones being tested, green rows passed, striped rows didn't.
In this lesson you'll:
- count rows that pass a test with COUNTIF, and write tests such as "more than 4", "not Low" and "at least the number in a cell";
- add up only the matching rows with SUMIF, and use several conditions at once with SUMIFS and COUNTIFS;
- build a summary table with one formula filled down, its ranges locked with
$; - read a nested IF as a decision tree, and write the same rule with IFS and AND.
It takes about 65 minutes. Each step ends with tasks in a live sheet: type a formula, press Enter, and the task ticks when the answer is right.
Takeaway: COUNTIF counts the rows that pass a test, SUMIF adds them up, and IF decides what to show.