Excel SUMIF, COUNTIF and SUMIFS Practice
Conditional functions answer questions about part of your data: how much did one region sell, how many tickets are still open, what is the average order for one channel, what is the best deal in one quarter. Instead of filtering by hand, you describe the condition and Excel does the rest, and the answer updates when the data changes.
These exercises cover the family in a sensible order: SUMIF and COUNTIF for one condition, AVERAGEIF and MAXIFS for other summaries, then SUMIFS with several conditions and with a window of dates. All of them are checked on hidden data, so a formula that only works for today's rows does not pass.
What these functions are for
- Total, count or average only the rows that meet a condition.
- Find the largest or smallest value for one person, product or period.
- Combine conditions: a region and a product, or a date range and a region.
- Build criteria from cells with &, so the same formula answers any period.
Tips before you start
- The range you test and the range you add must be the same size and line up row by row.
- Conditions with operators go in quotes, such as ">=100"; join a cell with & to use its value.
- Include both ends of a date window: use >= and <=, not > and <.
- An average of nothing is #DIV/0!: check that your condition matches at least one row.
Practice exercises, in a sensible order
Total Revenue for One Region
Add up only the values that meet a condition, using a formula that reads the data instead of repeating a number you worked out by hand.
Average Order Value with AVERAGEIF and AVERAGEIFS
Average only the rows that meet one or several conditions, and choose AVERAGEIF or AVERAGEIFS accordingly.
Largest and Smallest Deals with MAXIFS and MINIFS
Find the largest or smallest value among only the rows that meet one or more conditions.
Count Support Tickets with COUNTIF and COUNTIFS
Count rows that meet one or several conditions, and write criteria for 'not equal to' and 'starts with'.
Build a Region by Product Sales Summary with SUMIFS
Total values that meet two conditions, and lock the right parts of each reference so a single formula can be copied across a whole table.
Sales Between Two Dates with SUMIFS
Build comparison criteria from cell values with &, so a report window can change without rewriting the formulas.