How To Use The Excel Functions AVERAGEIF and AVERAGEIFS
Written by co-founder Kasper Langmann, Microsoft Office Specialist.
Finding the average in Excel is a very common task – and for that purpose the AVERAGE function nails it.
But sometimes you need a more capable function!
That’s where AVERAGEIF and AVERAGEIFS come in 🙂
If you’re familiar with SUMIF and SUMIFS, you already have a good idea of how these functions work.
If not, don’t worry; I’ll give you an overview before we get into the details.
Let’s get started…
Get your FREE exercise file
To get comfortable with AVERAGEIF and AVERAGEIFS, you need to use them for yourself.
So we’ve put together a free example workbook that you can download. We’ll be using it throughout the rest of the article, so download it now and follow along!
Getting detailed averages
with AVERAGEIF
As you might expect, the AVERAGE function calculates the average of a series of numbers.
That’s great, but what if you want to choose a specific subset of numbers for your average?
That’s where AVERAGEIF comes in. Let’s take a look at the syntax.
The syntax of the AVERAGEIF function
=AVERAGEIF(range, criteria, [average_range])
range contains the cells that the function will apply the criteria to. If there’s no average_range argument, these numbers will also be used to calculate the average.
criteria is a logical statement (like “>4”) that determines which numbers are included in the average.
average_range is an optional argument; if it’s included, the function will draw the numbers for the average from this range. We’ll see what this looks like in a moment.
Let’s see how this work in practice!
Open up the example workbook, and you’ll see a spreadsheet that contains units for four different regions over four quarters. Each region has multiple reports for units in each quarter.
We’ll use the AVERAGEIF function to find the average number of units per region.
First, click into a new cell and type “Average units region 2:” (so we don’t forget what we’re calculating here):
Now, click into another cell and type the following formula:
=AVERAGEIF(A2:A101, 2, C2:C101)
The range, A2:A101, is where our criteria will be applied.
The criteria is 2, so Excel will only average numbers for which there’s a value of 2 in the corresponding cell in column A.
average_range tells Excel where to get the numbers that it will average: column C.
Hit Enter and let’s see if it works:
Success!
Feel free to sort the spreadsheet by the Region column and use the AVERAGE function to confirm the result.
(Spoiler: it works.)
Kasper Langmann, Co-founder of SpreadsheetoZooming in with AVERAGEIFS
AVERAGEIF gives you a lot of power in selecting the numbers for your average.
But when you have a lot of data, you might need to get even more detailed…
That’s where AVERAGEIFS comes in.
The syntax of the AVERAGEIFS function
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2]…)
Like in the previous function, average_range is where Excel will look for the numbers to include in the average.
Note that this is the first argument in the formula, instead of the last.
criteria_range1 is the group of cells that Excel will apply the first criteria to.
criteria1, then, is the logical statement (like “<>5”) that Excel applies on that range.
You can include as many sets of criteria_ranges and criteria as you’d like. We’ll see how that works now.
Keeping the criteria ranges and criteria straight can be tough.
Be sure to take care if you’re using multiple criteria! Double-check your formulas before committing.
Kasper Langmann, Co-founder of SpreadsheetoIn the last example, we averaged the unit numbers from a specific region. Now we’ll use AVERAGEIFS to get the average numbers from a specific region in a certain quarter (region 3, in quarter 4).
Again, make sure to specify what you’re calculating so you don’t forget:
Then, we’ll need to craft our formula. Here’s what we’ll use:
=AVERAGEIFS(C2:C101, A2:A101, 3, B2:B101, 4)
This can be tough to read. Let’s break it down.
- Excel will be averaging the numbers in C2:C101.
- Only cells in the same row as the number 3 in A2:A101 will be included.
- Only cells in the same row as the number 4 in B2:B101 will be included.
In short, we’ll look at the average of numbers in column C that are in the same row as a 3 in column A and a 4 in column B.
Hit Enter to see what happens.
Again, feel free to sort the spreadsheet by columns A and B to check your result.
Kasper Langmann, Co-founder of SpreadsheetoA note about text
In our examples, we used very simple logical operators as criteria. We told Excel to only count cells that contained a single value (the equals sign was implied).
But you can use more complicated logical criteria as well.
In fact, you can also text as your criteria. And that means you can use all the great wildcards that come along with it.
For example, if you want to find the average age of people whose names start with B, your formula might look something like this:
=AVERAGEIF(A2:A51, “B*”, B2:B51)
Get specific with your averages
Filtering and sorting can help you get specific averages, but sometimes you want a function that does everything for you. AVERAGEIF and AVERAGEIFS are perfect for those situations.
And because there’s no filtering involved, adding new data to your set is no problem at all.
Once you get the hang of using criteria in your averaging functions, you’ll be getting all sorts of useful data from your spreadsheet in a few keystrokes!
Kasper Langmann, Co-founder of Spreadsheeto