AVERAGEIFS Function in Google Sheets

What is AVERAGEIFS Function in Google Sheets?

The AVERAGEIFS function in Google Sheets calculates the average of a range based on one or more criteria. It is similar to the AVERAGEIF function but with a minor difference! The AVERAGEIF returns the average of values that satisfy a single criterion, while the AVERAGEIFS function returns the average of values that satisfy multiple criteria.

The AVERAGEIFS formula in Google Sheets has the following arguments. First, we supply the range whose average is to be found. Next, we specify the range and the criterion to be applied to the range. We can supply multiple criteria separated by commas. Let us look at an example where we find the average score secured by a flower of the color “Yellow” and the flower being “Marigold.” Enter the following function.

=AVERAGEIFS(C1:C8,A1:A8, ”Yellow”,B1:B8, ”Marigold”)

Syntax

As discussed earlier, we use the AVERAGEIFS function in Google Sheets to get the average of a set of values subject to multiple criteria. The syntax of the function is as follows

AVERAGEIFS Formula in Google Sheets

=AVERAGEIFS(average_range, criteria_range1, criterion1, …)

Here, the criteria are referred to as criterion1, criterion2, .. and so on.

• average_range – The range for which we find the average.
• criteria_range1 – The range to check against criterion1.
• criterion1 – The range values will be checked with this pattern or test.
• criteria_range2, criterion2, etc is optional. These are the additional ranges and criteria to check.

How to Use AVERAGEIFS Function in Google Sheets?

To understand the function’s uses, let’s consider how to use the AVERAGEIFS function to calculate the average using multiple criteria.

• Enter AVERAGEIFS manually
• Access from the Google Menu bar

Entering AVERAGEIFS Manually

AVERAGEIFS calculates the average values of a range satisfying multiple criteria and has several practical uses. To understand how to enter the function manually, let us look at a simple example. In this example, we have to find the average score in Science of some students who belong to Grade 6 in a school. So, we will apply two criteria: Grade 6 and Science. 

Step 1: Arrange the data in a table, as shown below. The student’s name is in Column A, the Grade is in Column B, the subject is in Column C, and the score is in Column D.

Step 2: Now, let us enter the AVERAGEIFS formula. For this first enter =AVERAGEIFS( in an empty cell where you want to find the average. We will subsequently enter all the arguments. 

Step 3: The first argument is the range whose average we want to find. Here, it is the average of the scores. So, enter the first argument as D1:D10.

Step 4: The next argument is the criterion range and the criteria. We enter the range as B1:B10 and 6.

Step 5: The final step is to enter the last criterion, the range C1:C10 and “Science.” Close the parentheses.

Step 6: Press Enter. You get the average score of students in Grade 6 in science.

Access From the Google Menu Bar

Choose the cell where you wish to enter the AVERAGEIFS function.

Go to the following path: “Insert” tab => “Function” -> “Statistical” -> “AVERAGEIFS.”

Enter the required arguments within the parentheses and press Enter to get the result.

Examples

We have seen how to use the AVERAGEIFS function in the simplest way. Let us look at how to implement it with some suitable examples.

Example #1 – Calculate the average sales for a specific product within a certain price range

One practical use of AVERAGEIFS is finding the average sales for a specific product. Let’s look at a simple example. Here, we have to find the average sales of a Refrigerator in a supermarket that sells between $800 and $1200. So, we have three criteria to check here: the product name and the price range.

Step 1: Arrange the data in a table, as shown below. The product name is in Column A, the price is in Column B, and the sales value is in Column C.

Step 2: Now, let’s enter the AVERAGEIFS formula. We have to include multiple criteria as well. We enter the following formula in cell C5.

=AVERAGEIFS(C1:C13, A1:A13, “Refrigerator”, B1:B13, “>=$800” ,B1:B13, “<=$1200”)

Explanation:

It is very important to understand this formula.

1. The first argument C1:C13 shows the range where we want to find the average sales price.
2. The next argument A1:A13 is the range for the first criterion and the successive argument is the criterion, “Refrigerator.”
3. The third set of criteria is the range and the price. Here, we want the price to be >= $800.
4. The third set of criteria is the range and the upper price limit. Here, we want the price to be <= $1200.

Step 3: Press Enter. You get the average sales value of the refrigerators in the price range of $800 to $1200.

Example #2 – Using Wildcards in Criteria

You can also use the function with wildcards. The AVERAGEIFS function in Google Sheets allows you to use wildcards in its arguments to enable a match to partial text when applying criteria. The two main wildcards used are * and ?. In our example, we find the average pricing of flowers beginning with Da.

Step 1: Enter the details of the flowers and their pricing in the sheet.

Step 2: Use the following formula to find the average pricing of a particular set of flowers.

=AVERAGEIFS(B1:B7, A1:A7, “Da*”)

Step 3: Press Enter. You get the average pricing of flowers beginning with Da*. Here, it includes Dahlia, daffodils and daisies.

Thus, we can use * to match any text before or after a certain word. Also, similarly we can use ? in a text to match a specific character.

Example #3 – AVERAGEIFS with IFERROR Function

Given that we are finding the average by division, there are high chances of getting a #DIV/0! Error. Hence, we can use the function in conjunction with IFERROR to avoid errors. In this example, AVERAGEIFS returns the average of all cells that meet the specified criteria. IFERROR returns the value we specify if AVERAGEIFS evaluates to an error.

We have some regions, the number of orders, and their total sales. We can find some combination of sales averages. First, let us find the average sales of the West region with orders greater than 110.

Step 1: We have arranged the data in a Google Sheet.

Step 2: To find the average sales in the East region for orders greater than 110, let’s use the following formula.

=AVERAGEIFS(C1:C10, A1:A10, “East”, B1:B10, “>110”)

Step 2: Press Enter. You get the average sales value as $1395.

Step 3: Now, let us try the same for the West region for the same 110 orders. Repeat the same formula except replacing West for East.

=AVERAGEIFS(C1:C10, A1:A10, “West”, B1:B10, “>110”).  Press Enter.

Step 4: We get the #DIV/0 error since the criteria are unmet. So, we use the IFERROR function and enclose the AVERAGEIFS with it.

=IFERROR(AVERAGEIFS(C1:C10, A1:A10, “West”, B1:B10, “>110″),”NA”)

Step 5: Press Enter. You can find that the error message is replaced by NA.

Important Things to Note

1. We get the #DIV0! Error when the first argument for the range is left blank, or the criteria are not met.
2. When a cell in the criteria range is blank, the AVERAGEIFS function treats it as a zero value.
3. In AVERAGEIFS, each criteria_range must be the same size as the average_range.
4. When used with logical operators, all criteria arguments, even numbers, should be enclosed in double-quotes.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand AVERAGEIFS Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is AVERAGEIFS Function in Google Sheets. We learn how it to find the average with specific criteria using examples. You can learn more from the following articles. –

XIRR in Google Sheets

LOG in Google Sheets

NOT Function in Google Sheets