Naimul Hasan Arif
In our day to day life, we often face the necessity of calculating average. In Microsoft Excel, we can do that quite easily. But sometimes we have to calculate the average with certain conditions. In this article, we are going to explain 3 simple ways to calculate average if the number match criteria in Excel. I hope it will be helpful for you if you are looking to calculate the average with certain conditions.
For more clarification, I have organised a dataset of a company’s sales record in the Name, Area, and Sales columns.
Download Practice Workbook
3 Simple Ways to Calculate Average If Number Matches Specific Criteria in Excel
1. Calculating Average with AVERAGEIF Function for Single Criterion
In order to calculate the average with a certain condition, we can use the AVERAGEIF function. The whole procedure to calculate the average if a number matches a certain condition in Excel is stated below.
Steps:
- Select a cell where you want to have the average. In my case, I have selected cell D14 to have the output.
- Now, input the following formula in that cell:
=AVERAGEIF(C5:C11,D13,D5:D11)Here,
C5:C11 is the cell range of the function where the condition is applied.
D13 is the criteria of the AVERAGEIF function.
D5:D11 is the Average_range of the function from where the values are extracted for average calculation.
- Finally, press the ENTER button to have the average value.
Thus, we can simply calculate the average if the number satisfies a criterion in Excel.
Read More: Excel AVERAGEIF with ‘Greater Than’ and ‘Less Than’ Criteria
2. Average Calculation with AVERAGE and IF Functions
Another very simple yet effective way to average if number calculation in Excel is to use a combined formula with the AVERAGE and IF functions. Let’s walk through the following steps to have the average.
Steps:
- First of all, pick a cell where you want to have the average. Here, I have selected cell D14 to have the result.
- Next, apply the following formula in that cell:
=AVERAGE(IF(C5:C11=D13,D5:D11))Here, I have defined that if the value in cell D13 gets matched in the range C5:C11, then it will extract the value(s) connected with those cells from the range D5:D11. After that, the AVERAGE function will be in action and will calculate the average of the values.
- Now, simply hit ENTER to have the output on the screen.
Read More: How to Find Average If Values Lie Between Two Numbers in Excel
 3. Average If a Number Meets Multiple Criteria
We can also use a combined formula with the SUM, SUMIF, and COUNTIF functions to calculate the average value under certain conditions. The best part of this method is that we can use multiple conditions to calculate the average. Try to follow the following steps to execute the method properly.
Steps:
- Select a cell first where you want to have the average. Here, I have selected cell D14 to have the average.
- Now, input the following formula in that cell:
=SUM(SUMIF(C5:C11,D13:D14,D5:D11))/SUM(COUNTIF(C5:C11,D13:D14))
Formula Breakdown
SUMIF(C5:C11,D13:D14,D5:D11)—> The SUMIF function in the numerator part of the division returns the total sales for Illinois and Michigan separately in an array. The return output is as follows:
{63600;48532}
SUM(SUMIF(C5:C11,D13:D14,D5:D11))—> The SUM function then simply adds up the total sales found in the preceding step and returns $1,12,132.00.
COUNTIF(C5:C11,D13:D14)—> The COUNTIF function in the denominator counts all the cells containing Illinois and Michigan separately and thus it returns the output as:
{2;1}
SUM(COUNTIF(C5:C11,D13:D14))—> Now, the SUM function sums the total counts found in the previous step and returns 3.
SUM(SUMIF(C5:C11,D13:D14,D5:D11))/SUM(COUNTIF(C5:C11,D13:D14))—> Finally, the entire formula divides the total sales for Illinois and Michigan by the total counts and returns the output as $ 37,377.33
- Now, press ENTER to have the average.
Read More: How to Use Excel AVERAGEIF with Multiple Criteria (5 Examples)
Practice Section
For further expertise, you can practice here.
Conclusion
That’s all for this article. In this article, I have tried to explain 3 simple ways to calculate average if number satisfies a condition in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.
