How to Use AVERAGEIF Function in Excel

Everything you must be aware of AVERAGEIF as well as AVERAGEIFS functions that determine the average of values that satisfy each condition(s) within Excel.

If you’ve utilized SUMIF as well as COUNTIF within Excel and Excel, another statically categorical function that’s essential and beneficial to know is the AVERAGEIF. The Excel AVERAGEIF functions calculates the mean of the values you have specified or numbers within a set of cells, if the values satisfy the criteria you have specified.

Like SUMIF and COUNTIF, AVERAGEIF has two variants that it is available in: AVERAGEIF as well as AVERAGEIFS. One version is designed to handle one criteria, while the other (AVERAGEIFS) is designed to handle multiple criteria. AVERAGEIF function is utilized for analysis of data to determine the average of the values using the criteria.

Let’s say, for instance, that you’ve got a listing of the fruits that are available in a store and their prices over the month. You want to determine the average price of one of them (e.g. Mangoes). It is possible to use the AVERAGEIF function using Mangoes as the primary criteria and the price listing as the value range to determine the average price for only mangoes.

Calculate Average based upon a single Criteria using the AVERAGEIF Function

You can utilize the AVERAGEIF function within Excel to determine the average of values within a single range of cells, based on one criterion.

Syntax as well as Arguments to using the AVERAGEIF Function:

 =AVERAGEIF(range, criteria, [average_range])

Arguments:

• area (required) range (required) The range of cells which the specified criteria will be evaluated to determine the average (for the values contained in the cells that are corresponding). It could include numbers or arrays, as well as references made up of numbers.
• requirements (required) criteria (required) criteria that is evaluated with the categories to decide which cells will be averaged. It could be a number, the expression of a cell, a reference to a cell, or text.
• [average_range [average_range (optional) (optional) – It is the actual range of cells that has numbers that must be averaged. When this parameter is not included within the calculation formula then the specified number is utilized.

The formula’s criteria could be comprised of date, number or expression (with the logical formula), cells, texts, formula, or wildcards (*,?) to make partial matches. Date, number text, number, and cell references can be used in conjunction with logic operators like (>, <=,>=,<>, =) to create criteria.

Basic Example:

Let us discuss the way this function functions with a simple example before moving on to more complicated examples. In the following data we have an inventory of sales, and we want to calculate the average of those with values greater than 500. To achieve this we can apply the following formula:

=AVERAGEIF(A2:A15,">500")

Because that the values of the range as well as the the average range are identical in this data set We omitted the final argument, however the formula automatically takes the A2:A15 to be an range that is average as well as calculates the mean for numbers that are more that 500 (>500).

If the criteria is not an unambiguous number or cell reference, it is necessary to include the criteria within double quotes. The formula below gives similar results to the formula above, but we have identified the cell that has the criteria.

Numerical Criteria

AVERAGEIF function was created to test the criteria against a single column or range and then averages the results of a similar range, which means it requires two columns in order to accomplish this. Let’s say you have below table that has two columns. You want to determine the conditional average of one column:

Utilize the formula below to determine the average:

=AVERAGEIF(A2:A15,">10",B2:B15)

The formula examines the A2:A15 range A2:A15 for values that are greater than 10%, and if the values are discovered (to satisfy the criteria), it calculates the mean for the values that are in B2:B15. B2:B15.

Use this formula in which the argument for the criterion is referred to the cell that has the criterion:

=AVERAGEIF(A2:A15,E4,B2:B15)

The logical or comparators are often combined with dates and numbers to establish guidelines to use using the AVERAGEIF function. Here’s a list of the logical operators that you can employ:

Text Criteria

It is common to apply texts to determine the average of values that meet the requirements. For instance, the following data set contains a list of sales records and products.

To determine the average sales for the entire dairy industry, use the following formula:

=AVERAGEIF(A1:A14,"Dairy",B2:B15)

This formula computes the mean for the values of B2:B15 when the cell within the A1:A15 range A1:A15 have exactly the same value, ‘Dairy’ (criteria).

Example 2:

The text may be combined with the logic operators like ‘not equal to’ (<>) or equal to (=) to calculate the average of the values.

To determine the average sales for all items, excluding the diary product, use the formula below.

=AVERAGEIF(A1:A15,"<>Dairy",B2:B15)

This formula computes the mean of the values within B2:B15 when the cells within the A1:A15 range A1:A15 are not dairy-free (<>Dairy).

Date Criteria

When using AVERAGEIF, which is the AVERAGEIF feature, it is possible to may also apply dates as criteria like the numerical and text criteria. Let’s look at this table that lists the quantity of books that were that were sold on various dates.

With the formula above, we are able to determine the average of books that were sold prior to March 9 at 9:

=AVERAGEIF(A2:A15,D4,B2:B15)

To define the condition of date “on or before March 9′, in this we use the formula = March 9. (less that or equivalent to the 9th of March) that is entered into D4 in the column D4 and the cell reference is used to determine the criteria instead. The formula searches A2:A15 in the range A2:A15 for the date March 9 or earlier than March 9. If the conditions are fulfilled, the formula calculates the mean for numbers within B2:B15.

The similar result using this formula:

=AVERAGEIF(A2:A15,"<="&D4,B2:B15)

In this case, we join the logical operator and the cell's reference by with the operator '&. The text, logical operators and the date should be enclosed with double quotation marks within the argument for criteria.

AVERAGE If Blank or Non-blank Cells

By default by default, the AVERAGEIF function does not consider blank cells. However, sometimes it is necessary to determine the average value which corresponds either to empty or blank cells. In the case of criteria, we could make use of equal to in double quotation marks "" to locate blank cells or to find non-empty cells. to locate empty cells.

Average If Blank

If you wish to include cells that are completely blank (contain no data) within the range of examples, you can include "="in the criteria argument.

In the data set below in the below data set, we want to calculate the average of numbers that have nothing in the cells that are corresponding to them. To accomplish this we can apply the formula below:

=AVERAGEIF(A2:A15,"=",B2:B15)

The formula above calculates the mean of the interval B2:B15 however only when the cell column A 2:A15 within that row truly empty.

You can observe, the cells A5 A10, A10, and A12 are blank and therefore the formula uses the values 124, 45, and the number 147 from column B that corresponds to these cells and calculates the sum of those values. The results are returned to cell E4.

Sometimes, however, cells can appear blank, but they aren't completely empty, they could be filled with spaces characters, or strings that are empty that are returned from other operations.

To calculate an average value of the cells that correspond visually appealing blank cells, type in the empty double quotes as criteria in the below formula:

=AVERAGEIF(A2:A15,"",B2:B15)

Average If Not Blank

If you wish to determine the average value of blanks that are not empty, you can use the "not equal to" operator (<>) in the criteria argument. To find the average value of cells that are empty, use the following formula:

=AVERAGEIF(A2:A15,"<>",B2:B15)

This formula is used to determine the sum between the values in B2:B15 If the cell within the A2:A15 range A2:A15 is not empty.

AVERAGEIF using OR theorem (Multiple Criteria)

Typically typically, generally, the AVERAGEIFS function is utilized to manage multiple Criteria, but you can also utilize the AVERAGEIF function using OR or AND logic. It is possible to add at least two AVERAGEIF functions within the AVERAGE function to define various requirements. When any one of the given conditions are met the function calculates an average of the respective values.

For instance, we would like to determine the average of cells that meets either one of the requirements ('Dairy or grain') within the same interval. This is done by combining two AVERAGEIF functions that employ OR logic.

To determine the average of Grain or Dairy We can use this formula:

=AVERAGE(AVERAGEIF(A2:A16,"Dairy",B2:B16),AVERAGEIF(A2:A16,"Grain",B2:B16))

The formula above includes two AVERAGEIF functions: one to find the average of Dairy sales, and the other for determining the average of grain. First, the AVERAGEIF function is averaging of the cells of B1:B16 when the values in A1:A16 are equal to Dairy as well as the other AVERAGEIF function is averaging of cells in B1:B16 when A1:A16 values are correspond to those of Grain. We then average both of the results by placing both AVERAGEIF functions inside AVERAGE. AVERAGE function.

AVERAGEIF using AND the logic (Multiple Criteria)

There are times where you need to determine the average of cells that meet all of the requirements. To do this, you'll need to utilize the AVERAGEIFS function, which combines AVERAGE with AND logic in order to find the average of the values that satisfy all conditions.

Utilizing Wildcard characters to calculate Average Based upon Partial Match

Wildcards characters question mark (? ) or the asterisk (*), and tilde (~) are utilized in a criteria argument to determine a value that has an incomplete match.

The reason for 3 wildcard symbols found in Excel:

• The question mark (? ) can be used for matching a single letter or character with the string of text.
• Asterisk (*) is used to match any character using the string.
• Tilde (~) is used to locate a real question mark or an asterisk.

Let's see how we can use make use of wildcards to determine the average , based upon a match that is partial.

Asterisk (*)

Let's say you have the following table and want to determine the average number of 'Xerox'-related products.

However, the list includes a variety of models of xerox with different model numbers. If you just apply "Xerox" as criteria in the AVERAGEIF formula, you'll get a #DIV/0 error.

This is the reason you have to add the asterisk (*) to determine the average of the entire range of Xerox products.

Example 1:

To find the average of cells that partially meet the criteria specified Write the following formula:

=AVERAGEIF(A2:A19, "Xerox*", B2:B19)

In this case, we have included an Asterisk (*) symbol after the word "Xerox" in the criteria that means that the formula searches for the word 'Xerox' , followed by any combination of characters within the range A2:A19. If the criteria match it calculates the average using the values that correspond to the B2:B19 range. B2:B19.

Example 2:

It is also possible to use an Asterisk (*) between or after your text in order to partially match the word which contains other characters prior to or after.

=AVERAGEIF(A2:A19, "*rox*", B2:B19)

As you can see, the formula above yields the same results similar to the formula previously used. Since the formula searches for the names of products with the word "rox" in the middle, which is followed by any other characters. Then, it analyzes the average of the two values.

Example 3:

If you have to determine the average amount of all products, excluding any "Xerox" product, use the following formula:

=AVERAGEIF(A2:A19, "<>Xerox*", B2:B19)

This formula calculates the average of all the items, excluding Xerox.

(? ) Question marks (? )

A question mark (?) wildcard is used to match words using any missing character.

Example 1:

To calculate, for instance, the average of cells that meet the specified requirements using any 2 characters (in the wildcard position) Use the following formula:

=AVERAGEIF(A2:A19, "Xerox 1?8? ", B2:B19)

In the formula above we have used the two questions mark (?) as wildcards within the criteria ( Xerox 1?8?) to represent any characters that are not in their place. This formula is matched to values from cell A3, A4, and A10. Then, calculates the mean for the values that correspond to the B2:B19 range. B2:B19.

Example 2:

To determine the average of cells that have precisely 8 characters within the A2:A19 range.

=AVERAGEIF(A2:A19, "???????? ", B2:B19)

The formula above looks for any values that have 8 characters within the A2:A19 range. A2:A19 and, if it is it is found it calculates the mean of the values that are in the B2:B19 range. B2:B19.

Here's a list of wildcards and criteria examples:

Utilize to use the AVERAGEIFS function to handle multiple Criteria

AVERAGEIFS is a function that can be described as a sibling. AVERAGEIFS function is an identical (plural alternative) to the AVERAGEIF function that is available in Excel. In contrast to AVERAGEIF, which is a variant of the AVERAGEIF feature, AVERAGEIFS (plural counter) can handle multiple conditions at the simultaneously. This means that all required conditions have to be met for the cells to be averaged. This function was first introduced by Excel 2007 Excel 2007 and it is now available in all subsequent Excel versions.

Syntax of the AVERAGEIFS function

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

The AVERAGEIFS function comes with the following arguments:

• range of average (required) is the range of cells that contains the numbers you wish to average when multiple conditions are satisfied.
• Criteria_Range1 (required) (required) will be the initial range that the criteria that is first evaluated.
• Criteria1(required) This is the first of the criteria to be compared against the criteria_range1 in order to determine the cells that are to be averaged.
• Criteria2, ... - This is the second set of criteria in which the second criteria is evaluated. The criteria may be provided as an integer, logical expression or text value. It can also be a cell reference.
• criteria_range2 ... This is the second test of the criteria against the criteria_range2 to determine what cell types will get averaged.

Average Based on Multiple Criteria on a Single Column

You can use the AVERAGEIFS function to a single column to determine the average.

Let's suppose that you have the following table in which we can see the number of items in the inventory and the price at which they are sold.

We now need to determine the average cost of products that are more than 200 but less than 400 in quantity. To accomplish this we can apply the following formula:

=AVERAGEIFS(B2:B18, A2:A18, ">200", A2:A18,"<500")

In this case, we have defined the beginning and ending points for the range (Quantity) that we wish to determine the average of. Based on both of these criteria the formula will check every cell in column A to determine if it is higher than 200 or less than 400. If both of these conditions are met then it subtracts the selling price that is corresponding to column B. Then, it calculates the average of all the numbers that meet both of the criteria.

Average Based on Multiple Criteria (Text and Date Criteria)

AVERAGEIFS (Plural counterpart) AVERAGEIFS (Plural equivalent) is quite similar to the standard AVERAGEIF however, you'll have to include multiple conditions or criteria within the equation. However, all requirements or conditions must be met (AND logic) to calculate the average of the cells.

Example 1:

If you happen to have the following table and want to determine the average of sales across North America. North that are not more than 400 in number You can apply the formula below:

=AVERAGEIFS(C2:C18, A2:A18,"South",B2:B18,"<400")

This formula is used to determine every row to see if "South" is found in column A, and the column B value is lower than 400. If both conditions are satisfied then it subtracts the numbers that correspond to column C, and calculates the average of the columns.

Example 2:

Let's suppose we want to know the average amount of paper products that are shipped into Southern regions. Southern region that is greater than 2000. This can be done using this formula

=AVERAGEIFS(C2:C19, A2:A19, "Paper", B2:B19, "South", C2:C19, ">2000")

Here is the complete list of items that are in column A. It also includes the area in column B and the quantity of items that were shipped to the column C. The formula above includes three requirements - two textual entries as well as a combination of numbers and logical operators. The three requirements must be met for the formula to yield the desired result.

Average Cells are based upon Multi Criteria (Date criteria)

Similar to the AVERAGEIF function it is also possible to use Date to satisfy one of the many factors within the AVERAGEIFS function.

In the following example we will find the price of products that were sold prior to 14 April in the South region. To find this we can apply the formula below with several criteria, one for the date, and another for the regions:

=AVERAGEIFS(D2:D18,A2:A18,"<14-Apr-20",B2:B18,"South")

The formula says that the average_range is defined as column D, while criteria_range1 can be found in column A. the criteria1 is '14-Apr-20', while criteria_range2 is column B and the criteria2 is 'South'..

The formula determines whether the region 'South' and any date before '14-Apr-20' are within that same row. If those conditions are met the formula calculates the average using the values from column D.

Calculate the Average using AVERAGEIFS with OR logic

As we've seen earlier that Excel's AVERAGEIFS function makes use of and logic to determine the average of cells that satisfy all criteria. AVERAGEIF is able to handle only one condition, however you can use the AVERAGEIFS function to average cells based on different criteria using or logic. This can be accomplished by using multiple AVERAGEIFS function to the formula.

Example 1:

To determine the average amount of products that are sold in the north or south use the following formula:

=(AVERAGEIFS(B2:B18, A2:A18, "South")+AVERAGEIFS(B2:B18, A2:A18, "East"))/2

In the formula above, it is the initial AVERAGEIFS function determines the average amount in column B when the row that corresponds to column A is the same as the 'South'. The second AVERAGEIFS function calculates the average amount in column B when the row that corresponds to column A is the same as the 'East'. The results of both averages are added together and then divided by 2 to obtain the average amount of products that are sold in either the north or south.

Example 2:

If you need to include additional criteria in the formula, you could include the criteria in an array, so that you don't have to write the entire formula over and over.

Use this formula to achieve the same results as the earlier formula:

=AVERAGE(AVERAGEIFS(B2:B18, A2:A18, "South","East"))

In the previous example we had to tell Excel to add two distinct AVERAGEIFS together and then divide the result by two. In this example, Excel automatically finds the average of East as well as South by wrapping criteria within an array, as shown below. Additionally, this formula could drastically reduce the size of the formula, regardless of the number of criteria you need to add.

When Excel analyzes the formula it automatically assumes that we wish to calculate the AVERAGEIFS of every criterion of the array.


=AVERAGE(AVERAGEIFS(B2:B18, A2:A18,166.4,197))


Then, the outer AVERAGE function will then add all the results, giving you the final results.


=AVERAGE(166.4,197)



=181.7


Utilizing Wildcards in Multiple Criteria of the AVERAGEIFS function

Wildcards can also be used questions mark (? ) or and an asterisk (*), and tilde (~) in a variety of criteria of the AVERAGEIFS to calculate average values.

For instance, we would like to determine the average for all Xerox products that are sold in East and West

=AVERAGEIFS(C2:C19, A2:A19, "Xerox*", B2:B19, "? ?st")

In the formula above, the requirements Xerox* is compatible with all the Xerox products listed in column A. The ? most matches with South or East that correspond to one of the Xerox products. If both conditions are met it calculates the average amount in column C.

Flexible AVERAGEIF Formula in Excel

If you've got a huge dataset and want to determine the average of various items on that list You can make a an adaptable AVERAGEIF formula using the option to input the criteria.

Let's say you have the following data that includes the sales across different regions. Now, you're looking for the average number for various regions.

First, let's take the specific values we'd like to add to the drop-down menu from the column 'Region.

To do this Copy the column with values A2:A18 into a different place. Select the range of cells you want to copy and click on the tab 'Data' at the right side on the display.

In the Data tab Click the "Remove Duplicate button' in the Data Tools group.

In the remove Duplicates dialogue box ensure that the column's title (Region) is selected and select 'OK'.

We now have the list of unique values in the column titled Region.

You can also enter the unique numbers manually into an additional column.

The next step is to make a drop-down list based on the unique values we have gotten. To create this list, first choose a cell in which you would like to include your drop-down lists.

Go to the tab 'Data' and click on the "Data Validation" button on the ribbon.

Within the Data validation dialog Select "List" from the Allow drop-down menu under Validation Criteria.

Then you need to click on the 'Source field, then select the cell range you would like to include in the drop-down list.

Then, click 'OK'.

You now have a drop-down menu with all the regions of column A.

Then, we have to develop a formula that uses the drop-down list of cells D6.

To determine the sales volume average for the selected region use the formula below:

=AVERAGEIF($A$2:$A$18,D6,B2:B18)

In this case we have created the argument of range as complete by adding dollars ( $A$2:$A$18) which means it won't alter when the criteria is changed. This formula determines the mean within the range B2:B18, when the requirements (Region) within D6 is satisfied.

Then, you can use the drop-down menu to alter your parameters (Region) as well as the mean will automatically adjust for the criteria you have selected. Below is a screenshot of the average value for North region. North region.

If we alter the criteria to the East, the average will change automatically in accordance with the change.

If we wish to calculate an average of the northern regions, including the 'North' region and 'North-East' We can include the * (*) wildcard, followed by "North" in cell D6 as illustrated below.

The AVERAGEIF will display the average of the North and North-East regions.

What is the reason why the AVERAGEIF formula not working?

There are a variety of reasons your Excel AVERAGEIF function isn't working. Here's a list of the reasons to look out for:

AVERAGEIF Supports Only One Condition

In contrast to AVERAGEIFS (Plural) The AVERAGEIF function can only support only one criteria. If you input more than one criteria, you'll be notified that you have added too many arguments to this function warning'. Check your formula.

If you are looking to determine the mean using different criteria, then you could make use of the AVERAGEIF using multiple criteria, or using the AVERAGEIFS feature (AND the criteria).

Range/Criteria_range and Average_range Should be the Same Size

Within the AVERAGEIF formula, the range/criteria_range as well as the average_range must always be equal in size. If they are not you'd get an error code #VALUE.

As you can see in the following as you can see below, there are a lot of rows for argument range as well as the average_range are not the same which is why the error in value as the outcome (F6).

Also, ensure that you ensure that you have the identical number of rows and columns (same-size range) for the arguments range and average_range within the equation.

Additionally, you are not able to make use of arrays with ranges or average_range arguments within the AVERAGEIF function.

AVERAGEIF from a Different Workbook Doesn't Work

It is possible to use the AVERAGEIF function in order to compute the average of values that are from a different workbook or worksheet by using the worksheet as the argument. However, this function only works in the case that the workbook or worksheet that you're referring to is in the process of being opened.

For instance, the below formula calculates the average of values in a different workbook:

=AVERAGEIF([Book1.xlsx]Sheet1!A2:A18, "East", [Book1.xlsx]Sheet1!B2:B18)

This, [Book1.xlsx]Sheet1! refers to Sheet 1 in a different workbook titled Book1.xlsx.

It only works only if the sheet that the formula refers to is open. The range that is referenced turns into arrays that aren't compatible with the range or sum_range arguments. This is why the AVERAGEIF result is #VALUE! Error.

AVERAGEIF Criteria Argument

As you are aware that the AVERAGEIF function can be used to generate different kinds of arguments for criteria text, dates, numbers, cells, logic operators, wildcard characters, as well as other formulas

If the argument for the criteria contains an expression of text, a wildcard character, or other logical operator, followed by text, date, or number include the entire criteria with two quotation marks.

Here's a few examples:

=AVERAGEIF(A2:A18, "East? ", B2:B18)

=AVERAGEIF(A2:A18, "<>East", B2:B18)

=AVERAGEIF(A2:A18, "<=9/10/2020", B2:B18)

If the cell reference or another function is linked to this comparison operation, wrap the logical operator within two quotation marks. Add an ampersand (and) symbol to join (join) the logic operator with a reference or function , as illustrated in the following examples:

=AVERAGEIF(A2:A18, ">"&G6, B2:B18)

=AVERAGEIF(A2:A18, "<="&TODAY(), B2:B18)

AVERAGEIF Function is Not Recognizing Particular Text

AVERAGEIF functions are not case-sensitive, which means that it will evaluate lowercase and uppercase letters as the identical characters. For example, the string of text "WORKBOOK", "workbook" as well as "Workbook" will be treated as being equal. The system will determine the average of every text, no matter if it's the case of the text.

Things to Remember About the AVERAGEIF Function

• In the event that the cell(s) within the range contain Boolean value (TRUE or False) They are ignored.
• Blank cells within the range and average_range are not considered by AVERAGEIF.
• If the criteria you are defining is not met, AVERAGEIF treats it as a zero value.
• If the range is empty or has a text value then the AVERAGEIF formula will return the #DIV0! error that results.
• If none of the cells in the range match the requirements The formula will return the #DIV/0! error.
• If the values in the average_range are not able to be converted into numbers The formula returns #DIV0!.
• When the average_range and the average_range are not the same size, or if the text criteria are greater than length of more than 255 characters. The formula will return the #VALUE! error.
• If a date or number is joined to a logical operator in the argument for criteria, the entire argument should include double quotes. e.g. ">5/10/2022".
• As we have mentioned earlier that you can make use of wildcard characters such as questions marks (? ) as well as an asterisk (*) characters in the formula to serve as criteria argument.

That's it.