AVERAGEIF and AVERAGEIFS in Excel

Excel AVERAGEIF gives you the average of only the rows that meet a condition, such as the average salary in one department. AVERAGEIFS does the same with two or more conditions. If you already know SUMIF and SUMIFS, you know the shapes: this guide shows both on one staff table, plus how to handle the #DIV/0! error you get when nothing matches.

What does AVERAGEIF do in Excel?#

AVERAGEIF checks each row against one condition, then averages the numbers from the rows that match. The syntax:

=AVERAGEIF(range, criteria, [average_range])

If you leave out average_range, AVERAGEIF averages the cells it checked. AVERAGEIF and AVERAGEIFS have been in Excel since Excel 2007.

The examples use this staff table in A1:E9:

How to use AVERAGEIF: step by step#

Give AVERAGEIF the column to check, the value to match, and the column to average. For the average Engineering salary:

1. Click an empty cell, such as H2.
2. Type =AVERAGEIF( and select B2:B9.
3. Type ,"Engineering", and select D2:D9.
4. Close the bracket and press Enter.

=AVERAGEIF(B2:B9,"Engineering",D2:D9)

The result is 93,666.67: (68,000 + 118,000 + 95,000) ÷ 3. To show a whole number, wrap it in ROUND: =ROUND(AVERAGEIF(B2:B9,"Engineering",D2:D9),0) gives 93,667.

How to check an AVERAGEIF result#

AVERAGEIF is the same as a SUMIF divided by a COUNTIF with the same criteria. You can prove a result this way:

=SUMIF(B2:B9,"Marketing",D2:D9)/COUNTIF(B2:B9,"Marketing")

This gives 136,000 ÷ 2 = 68,000, the same as =AVERAGEIF(B2:B9,"Marketing",D2:D9). If the two ever differ, one of the ranges doesn't line up. It's also a handy way to explain your answer in a job test.

For comparison, the plain average of every salary is =AVERAGE(D2:D9), which gives 80,125. The AVERAGE function guide covers the basics.

AVERAGEIF with numbers and comparisons#

Comparisons go in double quotes, just as in COUNTIF and SUMIF. To average the salaries of everyone with 5 or more years:

=AVERAGEIF(E2:E9,">=5",D2:D9)

Four people match (Noor, Ivy, Lara and Oscar), so the result is 92,000. Oscar has exactly 5 years, so ">5" would leave him out and give 98,000 instead.

The last row has no average_range, so AVERAGEIF averages the salaries it checked: 92,000, 118,000, 84,000 and 95,000.

How to use AVERAGEIFS for multiple conditions#

AVERAGEIFS puts the numbers to average first, then one range/criteria pair per condition. To average Senior salaries in Finance:

=AVERAGEIFS(D2:D9,B2:B9,"Finance",C2:C9,"Senior")

Only Noor matches, so the result is 92,000. Here's another with a comparison, the average Engineering salary for people with 4 or more years:

=AVERAGEIFS(D2:D9,B2:B9,"Engineering",E2:E9,">=4")

Ivy and Milo match: (118,000 + 95,000) ÷ 2 = 106,500.

The order switch catches many people out. AVERAGEIF puts the average range last; AVERAGEIFS puts it first. It's the same rule as SUMIF and SUMIFS with multiple conditions.

How to build a department summary with AVERAGEIF#

Point the criteria at a cell, lock the ranges, and fill down. Put Finance, Marketing and Engineering in G8:G10, then type this in H8:

=AVERAGEIF($B$2:$B$9,G8,$D$2:$D$9)

Select H8:H10 and fill down (Windows: Ctrl+D; Mac: ⌘D). You get 74,666.67 for Finance, 68,000 for Marketing and 93,666.67 for Engineering. The $ signs keep the data ranges fixed; G8 moves to G9 and G10.

Who is paid above their department average?#

You can also use each row's own department as the criteria. In F2, type this and fill down to F9:

=D2>AVERAGEIF($B$2:$B$9,B2,$D$2:$D$9)

For each person, AVERAGEIF works out the average of their department, and the > comparison returns TRUE or FALSE. Noor shows TRUE (92,000 is above the Finance average of 74,666.67), and Kenji shows FALSE. This kind of "compare to the group" column is a common task in HR and sales reports.

Why does AVERAGEIF return #DIV/0!?#

AVERAGEIF returns #DIV/0! when no row matches, because an average of nothing means dividing by zero. For example, =AVERAGEIF(B2:B9,"Sales",D2:D9) fails because there's no Sales department.

First check that the criteria really should match: look for typos, extra spaces, or the ranges in the wrong order. If an empty result is normal, show a message instead:

=IFERROR(AVERAGEIF(B2:B9,"Sales",D2:D9),"No staff")

Other things to know:

• Blanks and text are skipped in the average range, so a missing salary doesn't count as zero.
• Zeros are counted. To exclude them, add a pair such as D2:D9,"<>0" in AVERAGEIFS.
• Same-size ranges. Make range and average_range cover the same rows, like B2:B9 and D2:D9.

Related conditional functions#

Averages are one of several "IF" summaries that share the same criteria rules. MAXIFS and MINIFS find the highest and lowest values for a group. Wildcards like * and ? let you average rows whose text only partly matches. To average or total between two dates, see sum by date range with SUMIFS; the same date criteria work in AVERAGEIFS. If your data has a few very large values, a median may describe it better; average vs median explains when.

Practise it#

Practise AVERAGEIF, AVERAGEIFS, MAXIFS and MINIFS in a live spreadsheet. You'll average salaries by department and years, combine two conditions, and fill a cell-based summary down a table, with feedback on every step.