AVERAGEIF and AVERAGEIFS examples: one condition or several, and why you get #DIV/0!

What do AVERAGEIF and AVERAGEIFS do?

AVERAGEIF takes the average of the numbers that meet one condition, AVERAGEIFS of the numbers that meet all your conditions. =AVERAGEIF(B2:B16,"Clothing",C2:C16) returns £7.67, the average price of an item of clothing. In a Dutch copy of Excel, the functions are called GEMIDDELDE.ALS and GEMIDDELDEN.ALS.

Every example uses a week of sales at the Second Chance charity shop in Gloucester, the fourth client in our Excel course. If you'd rather learn it step by step, follow the lesson on averages with a condition. If you're after the plain average, a weighted average or how AVERAGE itself works, read how to calculate an average. This article is about the versions with a condition.

How do you write AVERAGEIF and AVERAGEIFS?

The names differ by one letter, but the order is different too. AVERAGEIF has the numbers last, and that argument is optional (Microsoft). AVERAGEIFS has them first, followed by up to 127 pairs of a range and its criteria (Microsoft).

=AVERAGEIF(range,criteria,[average_range])
=AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)

With Dutch or Belgian regional settings you'd write =GEMIDDELDE.ALS(B2:B16;"Clothing";C2:C16) and =GEMIDDELDEN.ALS(C2:C16;B2:B16;"Clothing";E2:E16;"Sophie"). If you copy a formula, translate the function name and the separators.

How do you calculate an average per category?

Coordinator Yasmina at Second Chance wants to know what an item of clothing costs on average, and whether that's more than household goods. This is the whole sheet, with all fifteen sales. G2 holds a price limit of £10.

=AVERAGEIF(B2:B16,"Clothing",C2:C16)

The formula returns £7.67. AVERAGEIF finds Clothing in column B, gets six rows and divides their £46 by 6. With "Household" you get £8: the lamp, the vase and the bookend come to £24, divided by 3. So there's Yasmina's answer: household goods are just a little dearer.

Try it yourself with a category that has a story to tell. The exercise runs on Second Chance's real sheet, and you type the formula as you would in Excel.

Your formula ran in your own browser, on a real calculation engine. Then we run it again on a hidden version of the sheet, with other prices, so copying the answer won't help.

How do you use criteria in AVERAGEIF?

Just as in SUMIF: text and operators go in quotation marks, you join a cell on with &, and wildcards work too (Microsoft). If you test the same column you're averaging, you can drop the third argument.

The sheet as a whole comes to £8.93, with =AVERAGE(C2:C16). Grace is well above that, but it says little about her: she happened to sell the kids' bike. A comparison like that is only fair once you know what's behind the number.

How do you calculate an average with multiple conditions?

With AVERAGEIFS: the numbers first, then the pairs, just like SUMIFS. Only the rows where every condition holds count. =AVERAGEIFS(C2:C16,B2:B16,"Clothing",E2:E16,"Sophie") returns £6.33: Dress green, T-shirt XL and T-shirt M together make £19, divided by 3.

Between two values, you use the same column twice. =AVERAGEIFS(C2:C16,C2:C16,">=5",C2:C16,"<=10") returns £7 for the five items from £5 to £10. That's also how you leave outliers out: =AVERAGEIFS(C2:C16,B2:B16,"Toys",C2:C16,"<"&G2) returns £4.50 for the toys under the limit. Try it yourself.

How do you leave zeros out of an average?

Use ">0" as the criteria on the numbers themselves. At The Splash swimming pool in Tamworth, manager Dave recorded the visits of ten members. Laura's cell is empty, and Sara has never been. =AVERAGE(D2:D11) returns 9.78 visits, because Sara's 0 counts and Laura's empty cell doesn't. =AVERAGEIF(D2:D11,">0") returns 11 visits, the average of those who actually came.

Which number is right depends on your question. How often does a member come on average? Then Sara counts. How often does someone who comes, come? Then she doesn't. The function skips an empty cell anyway (Microsoft), so watch the difference between "not filled in" and "really zero". There's more on that in the article on AVERAGE.

Why isn't my AVERAGEIF working?

An average can go wrong in three ways: it gives an error, it works on the wrong rows, or it's mathematically right but misleading. That last one is the most dangerous, because Excel won't complain.

The formula returns #DIV/0!

No row meets the condition, or there are no numbers in the cells you're averaging (Microsoft). No furniture was sold, so =AVERAGEIF(B2:B16,"Furniture",C2:C16) returns #DIV/0!. That's an honest answer: there is no average. If you'd like to show something readable, write:

=IFERROR(AVERAGEIF(B2:B16,"Furniture",C2:C16),"No sales")

Don't replace it with a 0: an average of £0 isn't the same as no average. Microsoft warns that IFERROR suppresses every error (Microsoft). You can read when that does harm in IFERROR and IFNA.

The ranges are shifted

AVERAGEIF doesn't check the size of the last range: Excel starts at its first cell and takes a block in the same shape as the first range (Microsoft). So =AVERAGEIF(B2:B16,"Clothing",C3:C16) returns £7.33 instead of £7.67, with no message at all. AVERAGEIFS is stricter: each criteria range must be the same size and shape as the average range (Microsoft).

An average of averages is wrong

Take the average of the five category averages and you get £9.57, while the true average is £8.93. Every category then counts equally, whether it has one item like Electrical or six like Clothing.

Never average a set of averages. Go back to the raw rows, or weight each average by its count.

Which functions are like AVERAGEIF?

The family holds no secrets by now: the same criteria, the same pairs, a different calculation. On Second Chance's sheet, for Sophie's clothing:

To count, read COUNTIFS with examples. For amounts, there's SUMIFS with examples. Every function is on the Excel cheat sheet with its Dutch name. If you want to do all of this yourself, follow module 4 of the Excel course, and start with the free modules if you're new to Excel.