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…

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

Table of Contents

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!

Download the FREE Exercise File

Download exercise file
Download free exercise file

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):

cell-label

Now, click into another cell and type the following formula:

=AVERAGEIF(A2:A101, 2, C2:C101)

averageif

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:

averageif-result

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 Spreadsheeto

Zooming 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 Spreadsheeto

In 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:

cell-label-2

Then, we’ll need to craft our formula. Here’s what we’ll use:

=AVERAGEIFS(C2:C101, A2:A101, 3, B2:B101, 4)

averageifs

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.

averageifs-result

Again, feel free to sort the spreadsheet by columns A and B to check your result.

Kasper Langmann, Co-founder of Spreadsheeto

A 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