How to Calculate Average If Number Matches Criteria in Excel

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.

 Excel Average if Number


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.

Calculating Average with AVERAGEIF Function for Single Criterion

  • Finally, press the ENTER button to have the average value.

Excel Average if Number

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.

Average Calculation with AVERAGE & IF Functions

  • Now, simply hit ENTER to have the output on the screen.

Excel Average if Number

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

Average If Number Match Multiple Conditions

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.

Excel Average if Number

Read More: How to Use Excel AVERAGEIF with Multiple Criteria (5 Examples)


Practice Section

For further expertise, you can practice here.

For Practice


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.


Related Articles

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo