=AVERAGEIF(A2:A7,"North",C2:C7) averages the sales in C2:C7 on the rows where column A is North. It works like SUMIF, except that it divides the total by the number of matching rows.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Condition | Average | |
| 2 | North | Apple | 120 | North | 90 | |
| 3 | South | Pear | 45 | North, Apple | 80 | |
| 4 | North | Pear | 110 | Over 50 | 120 | |
| 5 | East | Apple | 55 | |||
| 6 | South | Apple | 195 | |||
| 7 | North | Apple | 40 |
F2 averages the three North rows, 120, 110 and 40, and shows 90. F3 needs two conditions, North and Apple, so it uses AVERAGEIFS: (120 + 40) / 2 = 80. F4 has no separate average range, so it averages the matching sales themselves.
AVERAGEIF and AVERAGEIFS syntax
=AVERAGEIF(range, criteria, [average_range])
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
The argument order is the same trap as SUMIF and SUMIFS: AVERAGEIF puts the range to average last (and lets you leave it out), AVERAGEIFS puts it first. The criteria are written the same way in both: "North", ">50", "<>0", "*apple*", or an operator joined to a cell, ">"&F5. Empty cells and text in the average range are skipped.
Average and ignore zeros
AVERAGE counts a 0 as a value, so two absent students with a score of 0 pull the class average down. Empty cells are different: AVERAGE skips them. =AVERAGEIF(B2:B7,"<>0") skips the zeros too.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Method | Result | |
| 2 | Ana | 80 | AVERAGE | 48 | |
| 3 | Ben | 0 | Ignore zeros | 80 | |
| 4 | Cara | 90 | Count of zeros | 2 | |
| 5 | Dan | Count of numbers | 5 | ||
| 6 | Eva | 70 | |||
| 7 | Finn | 0 |
AVERAGE divides 240 by 5, because Dan's empty cell is left out but the two zeros are counted, and shows 48. AVERAGEIF with "<>0" divides 240 by 3 and shows 80. Type 60 into B5 and both change; type 0 into B5 and only AVERAGE moves. To ignore zeros and negative numbers as well, use ">0".
Why AVERAGEIF returns #DIV/0!
When nothing matches, AVERAGEIF has nothing to divide by and returns #DIV/0!. Wrap it in IFERROR to show a dash, a message or an empty cell instead.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Formula | Result | |
| 2 | North | Apple | 120 | West average | #DIV/0! | |
| 3 | South | Pear | 45 | With IFERROR | No sales | |
| 4 | North | Pear | 110 | North max | 120 | |
| 5 | East | Apple | 55 | North min | 40 | |
| 6 | South | Apple | 195 | Apple max | 195 | |
| 7 | North | Apple | 40 |
#DIV/0! The formula divides by zero or by an empty cell.There is no West row, so F2 shows #DIV/0! and F3 shows the message. Change A3 to West and both show 45.
MAXIFS and MINIFS
F4 to F6 in the sheet above find the largest and smallest value with a condition. They use the AVERAGEIFS order, the range to search first: =MAXIFS(C2:C7,A2:A7,"North") returns 120 and =MINIFS(C2:C7,A2:A7,"North") returns 40. Unlike AVERAGEIF, they return 0 when nothing matches, not an error.
MAXIFS and MINIFS need Excel 2019 or later, or Microsoft 365. In Excel 2016 and earlier, =MAX(IF(A2:A7="North",C2:C7)) does the same; press Ctrl+Shift+Enter (Cmd+Shift+Enter on a Mac) to enter it in those versions.
Practice: average with two conditions
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Class | Score | Condition | Average | |
| 2 | Ana | A | 80 | Class A, no zeros | ||
| 3 | Ben | B | 75 | |||
| 4 | Cara | A | 0 | |||
| 5 | Dan | B | 60 | |||
| 6 | Eva | A | 90 | |||
| 7 | Finn | B | 0 | |||
| 8 | Gus | A | 70 |
Your turn: Average the scores of class A, leaving out the zeros (absent students). Write the formula in F2.
Average of averages: a common mistake
Averaging the averages of groups of different sizes gives the wrong overall average. North has three rows and South two, so each South row counts for more in the average of the two averages than it should.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | Formula | Result | |
| 2 | North | 120 | North | 90 | |
| 3 | South | 45 | South | 120 | |
| 4 | North | 110 | Average of the two | 105 | |
| 5 | South | 195 | All rows | 102 | |
| 6 | North | 40 |
E4 shows 105, E5 the real average of the five rows, 102. When groups differ in size, average the rows themselves with one AVERAGEIFS, or divide a SUMIFS by a COUNTIFS over the same conditions:
=SUMIFS(B2:B6,A2:A6,"North")/COUNTIFS(A2:A6,"North")
A score weighted by credits or by quantity is a different calculation again: that is a weighted average.
Frequently Asked Questions
What is the difference between AVERAGEIF and AVERAGEIFS?
AVERAGEIF takes one condition and puts the average range last: =AVERAGEIF(A2:A7,"North",C2:C7). AVERAGEIFS takes several conditions and puts the average range first: =AVERAGEIFS(C2:C7,A2:A7,"North",B2:B7,"Apple").
How do I average in Excel and ignore zeros?
Use =AVERAGEIF(B2:B7,"<>0"). It averages only the cells that are not 0. Empty cells are already left out by AVERAGE and AVERAGEIF, so only real zeros need the condition.
Why does AVERAGEIF return #DIV/0!?
No cell matched the condition, so Excel divides a sum of 0 by a count of 0. Wrap it to show something else: =IFERROR(AVERAGEIF(A2:A7,"West",C2:C7),"No data").
How do I find the maximum value with a condition?
Use MAXIFS, with the range to search first: =MAXIFS(C2:C7,A2:A7,"North") returns the largest North value. MINIFS works the same for the smallest. Both need Excel 2019 or later, or Microsoft 365.
