AVERAGEIF in Excel: how to use it
AVERAGEIF works out the average of only the rows that meet a condition, such as the average revenue for Leeds.
Try the first exercises for freeIn the lesson AVERAGEIF and MAXIFS
What does AVERAGEIF do?
AVERAGEIF calculates the average of only the rows that meet a condition. It's built the same way as SUMIF: first where Excel checks the condition, then the condition, and finally the column with the numbers.
With AVERAGEIFS you give more conditions, and then the column with the numbers comes first. MAXIFS and MINIFS work the same way, but give the highest or the lowest number.
When do you use AVERAGEIF?
- The average revenue of one region: =AVERAGEIF(A2:A100,"Leeds",C2:C100).
- The average score of those who passed: =AVERAGEIF(B2:B30,">=10").
- The average delivery time of one supplier in one year: =AVERAGEIFS(C2:C100,A2:A100,"Bolt",B2:B100,2026).
- The largest order from one customer: =MAXIFS(C2:C100,A2:A100,"Walker").
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
The average of the rows that meet one condition.
Filled in: =AVERAGEIF(A2:A6,"Leeds",C2:C6)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
AVERAGEIF | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | AVERAGEIF |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
range | The column in which you check the condition. | A2:A6 |
criteria | What you're looking for, such as "Leeds" or ">500". | "Leeds" |
[average_range] | Optional. The column with the numbers. With AVERAGEIF it's optional and comes last: leave it out, and Excel uses the range itself. With AVERAGEIFS it comes first. | C2:C6 |
, | A comma separates the arguments from each other. | , |
" " | You type text in quotation marks, such as "Leeds". A number or a cell, such as 500 or A2, you type without them. | |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. |
AVERAGEIF step by step
E1 contains =AVERAGEIF(A2:A6,"Leeds",C2:C6). The average of the two revenues for Leeds: (1200 + 410) / 2. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- In E1, type the function with a bracket.
=AVERAGEIF( - Select where Excel should look: the regions in A2:A6. Type a comma and the condition "Leeds".
=AVERAGEIF(A2:A6,"Leeds" - Type a comma and select the numbers you want the average of: the revenue in C2:C6.
=AVERAGEIF(A2:A6,"Leeds",C2:C6 - Close the bracket and press Enter: (1200 + 410) / 2 = 805.
=AVERAGEIF(A2:A6,"Leeds",C2:C6)
More examples
The average above a limit
E1 contains =AVERAGEIF(C2:C6,">300"). Without the third part, Excel uses the revenue column itself: the average of the four revenue figures over 300.
Two conditions with AVERAGEIFS
E1 contains =AVERAGEIFS(C2:C6,A2:A6,"Bolt",B2:B6,2026). The average delivery time of Bolt in 2026: two deliveries, of 3 and 5 days. With AVERAGEIFS, the column with the numbers comes first.
The highest with a condition
E1 contains =MAXIFS(C2:C6,B2:B6,"Monitor"). The highest revenue from the monitors: 410 in Leeds. MINIFS would give 340.
Several conditions: AVERAGEIFS
AVERAGEIFS takes more than one condition. The range with the numbers then comes first, just like in SUMIFS: =AVERAGEIFS(C2:C6,A2:A6,"Leeds",B2:B6,"Laptop").
The highest and lowest with a condition
MAXIFS and MINIFS are built like AVERAGEIFS: =MAXIFS(C2:C6,A2:A6,"Leeds") gives the highest revenue for Leeds. You need Excel 2019 or newer.
Where it often goes wrong
- If no row meets the condition, you get #DIV/0!.
- In AVERAGEIF the range with the numbers comes last, in AVERAGEIFS it comes first.
Practise this in the lesson AVERAGEIF and MAXIFS. The first exercises are free.
Frequently asked questions
Does an empty cell count towards the average?
No, Excel skips empty cells. A 0 does count, and pulls the average down.