Difference Between AVERAGEIF and AVERAGEIFS in Excel

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.

Download Practice Workbook

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

4 Cases to Show the Difference Between AVERAGEIF and AVERAGEIFS Functions in Excel

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.

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.

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: Excel AVERAGEIFS with Multiple Criteria in Same Range

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.

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.

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: How to Use AVERAGEIFS Between Two Values in Excel

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.

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.

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.

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

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.

• Afterward, press ENTER and get the answer.

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

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: [Fixed!] How to Fix AVERAGEIFS Value (#VALUE!) Error in Excel

Practice Section

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

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. Visit our website, ExcelDemy, a one-stop Excel solution provider, to find out about diverse kinds of Excel methods. Thanks for your patience in reading this article.

Related Articles

• How to Apply AVERAGEIFS Function Between Two Dates in Excel
• AVERAGEIFS Function with “Not Equal to” Criteria (3 Examples)
• How to Use AVERAGEIFS Function for Multiple Columns