Course / Conditional Math

SUMIF and AVERAGEIF

Add up or average just the rows that meet a condition. Intermediate · 5 min · SUMIF, AVERAGEIF, sum_range

SUMIF adds up only the cells that pass a test. Its third argument is optional:

• =SUMIF(range, criteria) tests a range and adds the matching cells in that same range
• =SUMIF(range, criteria, sum_range) tests one column but adds the matching cells from another

AVERAGEIF has exactly the same shape but returns the average. The criteria follow the COUNTIF rules: "Harbour", ">1000", wildcards or a cell reference.

Steps

1. Your food truck has had a busy fortnight. In G2, add up the revenue in D2:D11 from days that took more than $1,000. The test and the sum use the same column, so two arguments are enough.

2. Now total the revenue for each location. In G6, test the locations in column B against F6 and add the matching revenue from column D. Lock both ranges with $ so you can copy the formula down to G8.

3. Which spot has the best typical day? In H6:H8, use AVERAGEIF to find the average daily revenue at each location. It takes the same three arguments.

4. Last one. In G3, find the average number of customers on days with 100 or more customers, using C2:C11 for both the test and the average.

Remember

• =SUMIF(range, criteria) adds the matching cells themselves.
• =SUMIF(range, criteria, sum_range) tests one column and adds another.
• AVERAGEIF has the same shape and returns the average.
• Point the criteria at a label cell and lock the ranges to build a summary table.

Previous: COUNTIF · Next: COUNTIFS, SUMIFS and MAXIFS