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.