In Microsoft Excel, we can calculate the average (the arithmetic mean) with the help of the **AVERAGE **function. But when you approach an advanced level, e.g. applying some criteria or finding the average of blank cells, you can’t find the mean with only the **AVERAGE** function. In that case, using the **AVERAGEIFS **function would be beneficial for you. Though both these functions offer the average of specific criteria, there are some significant differences between the **AVERAGEIF** and **AVERAGEIFS **functions. In this article, we’re going to show you the difference between **AVERAGEIF** and **AVERAGEIFS** with relevant examples. So, let’s get started.

**Table of Contents**Expand

## Difference Between AVERAGEIF and AVERAGEIFS Functions in Excel: 4 Cases

**The AVERAGEIF function **is like a baby walking. On the other hand, **the AVERAGEIFS function** is like a human running. Briefly, the **AVERAGEIFS** process helps you estimate the average of multiple criteria, whereas the **AVERAGEIF** only calculates for single criteria. As we said earlier, by using **AVERAGEIFS**, you can calculate for the more advanced dataset. Here we have used a dataset of **Student’s Marks** in exams and their **Passing Year**.

Not to mention, we have used the ** Microsoft 365** version. You can use any other version according to your preferences.

### 1. AVERAGEIF vs AVERAGEIFS While Considering Criteria

Basically, the **AVERAGEIF function** estimates the average based on a single criterion. But we can’t get the average with the **AVERAGEIF** function when it comes to multiple criteria. In this case, the **AVERAGEIFS** function comes as a savior. The **AVERAGEIFS** function solves our problem by calculating multiple criteria. Mainly, this is the basic difference between these two functions. Follow the steps to get a proper idea of this factor.

**📌**** Steps:**

- First of all, go to cell
**E15**and write up the formula.

**=AVERAGEIF(D5:D13,”Pass”,C5:C13)**

Here,

**D5:D13** = The criteria range.

**C5:C15 **= The average range.

The **AVERAGEIF(D5:D13, “Pass”, C5:C13) **syntax will look for the text “Pass” in the **criteria_range** **D5:D13** and then take the marks for “Pass” from the **average_range **of **C5:C13**. Lastly, it shows the average for our given criteria.

- Now, we will try to insert another criterion. We want to evaluate the average of the passing out
*Students’ Marks*from*2020*. That means we don’t want to count the successful students of 2018, and 2019. We can’t do this with the**AVERAGEIF**function. Rather we need to perform an**AVERAGEIFS**formula regarding this matter. Go through the steps.

**📌**** Steps:**

- In the very beginning, go to cell
**E16**and write the formula.

**=AVERAGEIFS(C5:C13,E5:E13,”>=2020″,D5:D13,”Pass”)**

**Formula Breakdown:**

**AVERAGEIFS(C5:C13,E5:E13,”>=2020″,D5:D13,”Pass”)→ **The **average_range** is **C5:C13, **the **criteria_range1** is** E5:E15** and the **criteria1** is **>=2020**. The function will take the value. Then **D5:D13 **is the **criteria_range2** and the **criteria2** is** “Pass”**. Eventually, the function will look for the value that satisfies both criteria. Finally, calculate the average.

*Note: **The formula syntax of the AVERAGEIFS function is slightly different as it takes the average_range at first whereas the AVERAGEIF function takes it at the end.*

- Finally, you will get your average for both the functions but as you can see the results are different as we entered 2 arguments in the
**AVERAGEIFS**function.

**Read More:** AVERAGEIFS for Multiple Criteria in Different Columns in Excel

### 2. AVERAGEIF vs AVERAGEIFS for Blank Cells

While there are blanks in your sheet, Excel’s **AVERAGE** function can’t remove them while calculating. But the **AVERAGEIF** and **AVERAGEIFS** functions can. The **AVERAGEIF** function calculates the average ignoring the blank cells. But it can calculate the average of blank cells, whereas the **AVERAGEIFS** function can calculate the average either for the blank cells or ignoring the blank cells. Let’s clear up the confusion with an example in the following steps.

**📌**** Steps:**

- First of all, in this image, we can see there are some blank cells. We will evaluate the average by ignoring the blanks.
- Consequently, write down the below formula for doing it.

**=AVERAGEIF(C5:C13,”<>0″)**

The **AVERAGEIF(C5:C13,”<>0″) **syntax will take the value greater than 0 and display the average.

- Eventually, you will get the result by pressing
**ENTER**.

- On the other hand, the
**AVERAGEIFS**process will calculate for blank text. Suppose you want to know the average of the values that contain a blank cell. The**AVERAGEIF**function can’t help you out in this matter. But the**AVERAGEIFS**can.

**📌**** Steps:**

- Initially, select cell
**C15**and enter the formula.

**=AVERAGEIFS(C5:C13, B5:B13,”=”)**

Here, the function takes the** average_range **as **C5:C13 **and looks for the blank cells in the **criteria_range** of **B5:B13**. Then it will estimate the average for blank cells,

- Press
**ENTER**.

- Subsequently, you will get your result.

**Read More: **Excel AVERAGEIFS with Multiple Criteria in Same Range

### 3. AVERAGEIF vs AVERAGEIFS While Excluding Zero and Blank Cells Simultaneously

Both the **AVERAGEIF** and** AVERAGEIFS** functions can evaluate the average excluding zeros. But the extra advantage you will get from the** AVERAGEIFS** is that it can calculate the average by excluding zeros and blank cells both in the same sheet. Pretty nice, right? We will demonstrate to you the steps regarding this.

**📌**** Steps:**

- Firstly, select cell
**C15**and insert the below formula.

**=AVERAGEIF(C5:C13,”>0″)**

Here, It will take the value greater than zero and calculate the arithmetic mean.

- Press
**ENTER**.

- Eventually, you will get the results.

- In the
**AVERAGEIFS**function, we have entered the formula.

**=AVERAGEIFS(C5:C15,C5:C15,”>0″,C5:C15,”<>”””)**

Here, we search the result for multiple arguments. One is for the value in the** cirteria_range **greater than zero, and the other is for blanks. For that, we insert **“<>”** as our second criterion.

- Gradually, after pressing
**ENTER**you will get your preferred result.

### 4. AVERAGEIF vs AVERAGEIFS with OR Type Logic

The **OR logic** means adding some extra numbers with the same formula. Let’s clear up the confusion. Suppose you have a dataset where 2 specific **Subjects’ Marks** are given. Now, you want to estimate the average of **Physics** and** Math** individually and then add them to get the final average. You can perform this operation with both functions. But the **AVERAGEIFS **function saves valuable time as the formula is very easy to write and insert. On the other hand, the **AVERAGEIF **formula with **OR** type logic is big to write. It kills our time and there are possibilities to show errors in that case. See the examples we have provided with the steps.

**📌**** Steps:**

- Primarily, select cell
**C15**and input the formula.

**=(AVERAGEIF(D5:D13, “Physics”, C5:C13)+AVERAGEIF(D5:D13, “Math”, C5:C13))/2**

**Formula Breakdown:**

**AVERAGEIF(D5:D13, “Physics”, C5:C13)→ **The function will look for the **criteria** **Physics **from range **D5:D13 **and then take the **average_range **for **Physics** from **C5:C13** and average them.

**AVERAGEIF(D5:D13, “Math”, C5:C13)→ **It will look for the **criteria** **Math **from range **D5:D13 **and then take the **average_range **for **Math** from **C5:C13** and average them.

**(AVERAGEIF(D5:D13,”Physics”,C5:C13)+AVERAGEIF(D5:D13,”Math”,C5:C13))/2→ **After that, it will add both the averages and divide it by 2 for the final average.

- Afterward, press
**ENTER**and get the answer.

- On the other hand, in the
**AVERAGEIFS**function, the formula is likely to be.

**=AVERAGE(AVERAGEIFS(C5:C13, D5:D13,{“Physics”, “Math”}))**

Here,

The function will take the value for both the subjects of **Physics **and **Math **from the **criteria_range **of **D5:D13** and then average them from the **average_range **of **C5:C13**.

- Then, press
**ENTER**. - Thus, you will get the result.

Now, as you can see, both the functions delivered the same output, but the **AVERAGEIFS** formula is pretty simple and handy compared to the formula of the** AVERAGEIF **function. Hopefully, we think that you get the difference here.

**Read More:** How to Use Excel AVERAGEIFS Function with Multiple Criteria

## Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

**Download Practice Workbook**

Download the following practice workbook. It will help you to realize the topic more clearly.

## Conclusion

That’s all about today’s session. And these are some easy methods on the difference between **AVERAGEIF **and** AVERAGEIFS**. Please let us know in the comments section if you have any questions or suggestions. For your better understanding, please download the practice sheet. Thanks for your patience in reading this article.

## Related Articles

- How to Use AVERAGEIFS Function for Multiple Columns
- How to Use AVERAGEIFS Between Two Values in Excel
- How to Apply AVERAGEIFS Function Between Two Dates in Excel
- [Fixed!] How to Fix AVERAGEIFS Value (#VALUE!) Error in Excel
- AVERAGEIFS Function with “Not Equal to” Criteria

**<< Go Back to Excel AVERAGEIFS Function ****|**** Excel Functions ****|**** Learn Excel**

Quite Helpful

Thank You

Thanks for your appreciation. It means a lot. You can explore our website to find out extraordinary techniques in Excel.