The AVERAGEIF and AVERAGEIFS Function in Excel

The AVERAGEIF and AVERAGEIFS function in Excel are predefined functions that calculate the arithmetic mean based on specified criteria. You can use either function to calculate the average value of a range of data when they meet specified conditions.

For example, you may want to know the average score of Male students in a class. To evaluate the average score based on the condition (Male students), you will employ the AVERAGEIF function. If there is more than one criterion, you employ the AVERAGEIFS function in Excel.

The AVERAGEIF function is a legacy function while the AVERAGEIFS function was introduced in Microsoft Excel 2007. Both functions are useful when evaluating data that has inherent criteria.

Whereas the AVERAGEIF function accepts one criterion, the AVERAGEIFS function accepts multiple criteria.

In this tutorial, you will learn how to use the AVERAGEIF and AVERAGEIFS function in Excel.

The AVERAGEIF in Excel

The AVERAGEIF in Excel is used to calculate the average or mean values of a range of cells, based on specified criteria. For example, the worksheet below displays the English score of students based on sex and class. To calculate the average score of students in SS2A, you will use the AVERAGEIF function as follow: =AVERAGEIF(D2:D18, “SS2A”, E2:E18)

The above function tells Excel to calculate the average value in the range E2 to E18 if the value in the range D2 to D18 is SS2A. To execute the function, Excel will trace where SS2A appears in column D and select the corresponding value in column E. When all items are selected, they will be summed together and the average value will be calculated.

You can also perform AVERAGEIF in one column only. For example, I can decide to get the mean value of those who scored below 50 in Maths. In this case, it doesn’t matter which class the student belongs to.

To do this, I will execute the following function: =AVERAGEIF(E2:E18,“<50”). In this example, my criterion is less than 50. The output gave a value of 35.75 as shown below.

Notice that only one range was employed in this example (E2:E18), hence, the Average_range was not used. This leads us to the AVERAGEIF syntax.

The AVERAGEIF syntax

The syntax for the AVERAGEIF function is given by: AVERAGEIF(range, criteria, [Average_range])

Where:

• range: is an argument that must be specified (required). This argument represents the range of cells that should specify the AVERAGEIF criteria. See the example above. Such ranges can contain text, numbers, references, and dates.
• criteria: This is also a required argument that must be specified in the function. The criteria can be a number, text, cell reference, or function. It is used to define the cells that average will be calculated. When the criteria specified are met, the average of the corresponding cells will be computed.  The criteria argument can accept wild characters such as ? or *.
  • The question mark (?) is used to match a single character while the asterisk (*) is used to match a sequence of characters.
  • A single character match can be like this, “SS?A”. This will match SS1A, SS2A, SS3A, etc.
  • A sequence character match (*) can be represented thus:
    • *le: referring to cells that end with le. E.g., female, male, gazelle, etc.
    • SS*: referring to cells that begin with SS. E.g., SSS3, SS1B, etc,
    • *m*: referring to cells that contain ‘m’. e.g. Amaka, Amara, etc.
• average_range: This is an optional argument in the AVERAGEIF in Excel. This means that this argument may be omitted. However, you can omit it if the average_range is the same as the range argument. Generally, if this argument is omitted, Excel will always compute the argument based on the range argument. But if the range argument is not a number, an error will occur. It is advised that the average_range be the same size and shape as the range argument. If their sizes differ, it might affect the performance of the function.

Example of AVERAGEIF in Excel

The worksheet below displays the sales record of O’ classical Enterprises in three regions: East, West, and South. The general manager wants to know the average sales per region for the month under review. You can use the AVERAGEIF function to analyze the data whereby the regions will form the criteria to compute the average.

The solution can be arrived at by following the following steps:

1. Create three columns to represent the East, South, and West regions as shown in the worksheet above.
2. Under the east region cell, enter the formula: =AVERAGEIF($C$3:$C$58,”East”,$D$3:$D$58)
3. Copy the above formula and paste it into the remaining two cells.
4. Under the South cell, change “east” to “south”, and under the West cell, change “east” to “west”.
5. Press ENTER after each change to calculate the average.

The AVERAGEIFS in Excel

The AVERAGEIFS function in Excel is used to calculate the mean values of a range of cells, based on more than one criteria. The AVERAGEIFS function can allow up to 127 range/criteria pairs when analyzing data in Microsoft Excel. Unlike the AVERAGEIF function where the range to be calculated appears last, in the AVERAGEIFS, the average_range comes first.

For example, the worksheet below displays the Maths score of students based on sex and class. To calculate the total score of female students in SS2B, I will use the AVERAGEIFS function as follow: =AVERAGEIFS(E2:E18,C2:C18,”Female”,D2:D18,”SS2B”)

The above function tells Microsoft Excel to calculate the average of the values in the range E2 to E18. This average will be calculated if the value in the range C2 to C18 is Female, and the value in D2 to D18 is SS2B. This means that the calculation will be performed only if the Female in columns C matches SS2B in column D.

To execute the function, Excel will trace where Female appears in column C, and where SS2B appears in column D and select the corresponding value in column E. When all items are selected, they will be summed and the average value calculated and displayed.

The AVERAGEIFS syntax

The syntax for the AVERAGEIFS function is given by: AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2, … criteria_rangen, criterian])

Where:

• average_range: is an argument that must be specified (required). This argument represents the range of cells that has values for calculating the average value. See the example above. Such ranges should contain numbers.
• Criteria_range1: This is also a required argument that must be specified in the function. The criteria_range1 and criteria1 set up a search pair so that the item in criteria1 will be searched for in criteria_range1. If a match is found, the average calculation takes place, else, the average calculation is omitted.
• criteria1: criteria1 is a required argument. It defines which cells in criteria_range1 that average value will be calculated in the function. The criteria can be a number, text, cell reference, or function. When the criteria specified are met, the mean value will be calculated in the corresponding cells. 
  • Criteria can accept wild characters such as question mark (?) or asterisk (*). The ? is used to match a single character while * is used to match a sequence of characters.
  • A single character match can be like this, “SS?A”. This will match SS1A, SS2A, SS3A, etc.
  • A sequence character match (*) can be represented thus:
    • *le: referring to cells that end with le. E.g., female, male, gazelle, etc.
    • SS*: referring to cells that begin with SS. E.g., SSS3, SS1B, etc,
  • *m*: referring to cells that contain ‘m’. e.g. Amaka, Amara, etc.
• criteria_range2, criteria2 … criteria_rangen, criterian: These are optional ranges and criteria up to 127 range/criteria pair. It is used to define the number of range/criteria pairs for the AVERAGEIFS function in Excel. Any of them can be omitted in the function. It is advised that the average_range be the same size and shape as the criteria_range, criteria pair. If their sizes differ, it might affect the performance of the function.

Take note of the following when using the AVERAGEIFS function in Excel:

• Always ensure that text values are in quotation marks when specifying criteria. E.g. “Male”.
• Use wild characters to help match multiple cells, especially, when computing a large set of data.
• Ensure that the average_range contains the same number of columns and rows as the criteria_range argument.
• You can introduce the comparison operators when using the AVERAGEIFS function. For example, =AVERAGEIFS(A2:A10,B2:B10, “>=25”,…)

Example of the AVERAGEIFS function in Excel

The worksheet below displays the sales record of O’ classical Enterprises in three regions: East, West, and South. Each region is controlled by two sales representatives. The general manager wants to know the average sales per Sales Rep. in each region for the month under review.

You can use the AVERAGEIFS function to analyze the data whereby the sales reps and the regions will form the criteria for calculating the average.

Follow the steps highlighted below to solve the above problem.

1. Create three columns to represent the East, South, and West regions as illustrated in the worksheet above.
2. Create six rows to represent each Sales Rep. in each region as shown in the worksheet above.
3. Under Harry Johnson and the East region cell, type in the formula: =AVERAGEIFS($E$3:$E$58,$C$3:$C$58,”East”,$D$3:$D$58,”Harry Johnson”)
4. Copy the above formula and paste it under the East region corresponding to Catson Veroni.
5. Change the text “Harry Johnson” to “Catson Veroni” and press ENTER.
6. Under Uche Golden and the South region cell, type in the formula: =AVERAGEIFS($E$3:$E$58,$C$3:$C$58,”South”,$D$3:$D$58,”Uche Golden”)
7. Copy the above formula and paste it under the South region corresponding to Barry Inkline.
8. Change the text “Uche Golden” to “Barry Inkline” and press ENTER.
9. Copy the above formula and paste it under Philo Metas and the West.
10. Change the criteria texts: “South” and “Uche Golden” to “West” and “Philo Metas” respectively. The formula will look like this after the change: =AVERAGEIFS($E$3:$E$58,$C$3:$C$58,”West”,$D$3:$D$58,”Philo Metas”)
11. Copy the above formula and paste it under the West region corresponding to Andre John.
12. Change the text “Philo Metas” to “Andre John” and press ENTER.

The result shows the average sales of each sales representative and the average sales per region in the period under review. Harry Johnson made the highest average sales while Uche Golden made the least average sales. The East region has the highest average sales while the South region has the least.

Conclusion

The AVERAGEIF and AVERAGEIFS function in Excel are very useful in data analysis. It helps us to compute the mean value based on specified criteria. We can use it to compute templates and distribute them to users for effective data analysis.

The AVERAGEIF function requires one criterion while the AVERAGEIFS require multiple criteria. The major difference in their syntax is that:

• In the AVERAGEIF function, the average_range could be optional and comes as the last argument.
• In the AVERAGEIFS function, the average_range is required and comes as the first argument.

As you have understood how to use these functions, you can start implementing them to relevant data. I will advise you to try as many examples as possible on your own to consolidate your understanding. If you encounter difficulties when applying them to relevant data, reach us through the comment or by contacting us.

Do not forget to check our previous tutorials in this series. Next week, we shall discuss the conditional functions in Excel. Meanwhile, share this with your friends and colleagues.