**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.)

**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.

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:

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.

**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!