Sometimes, you need to find out the average of your dataset excluding certain cells. In that case, you can use the AVERAGEIFS function. The AVERAGEIFS function returns the average of the cells of an array that satisfy one or more given criteria. The criteria can be of the same array or a different array. In this article, we will show you how to use the AVERAGEIFS function if the value is not equal to blank or any certain value in Excel. I hope you find this article very informative and gain lots of knowledge regarding the topic.
How to Use AVERAGEIFS Function with “Not Equal to” Criteria in Excel: 3 Examples
To use AVERAGEIFS with not equal to a certain value, we have found three suitable examples through which you have a piece of complete knowledge. In this article, we would like to 3 cases when we use the AVERAGEIFS function for a single column, multiple columns, and multiple criteria. In every case, we would like to use not equal to condition.
1. Finding Average Marks of Each Exam
Our first example is based on finding average marks for each exam. Here, we take a dataset that includes several students and their marks in different exam segments. Some of them are absent from certain exams. We would like to find the average marks when it is not equal to absent text in the dataset. That means when it gets the absent text in the dataset, it will ignore them and find the average for other cases. To understand the example, follow the steps.
Steps
- First, we would like to find the average excluding the absent text.
- Select cell C13.
- Then, write down the following formula.
=AVERAGEIFS(C5:C11, C5:C11, "<>Absent")
- Press Enter to apply the formula.
- After that, drag the Fill Handle icon up to cell E13.
🔎 Breakdown of the Formula
AVERAGEIFS(C5:C11, C5:C11, “<>Absent”): The AVERAGEIFS function takes the average range and criteria range and returns the required average. Here, the average range is C5 to C11, and our criteria range is C5 to C11 when it is not equal to Absent text. So, the AVERAGEIFS function takes this range and searches the given criteria. Finally, it returns the required average excluding the Absent text.
2. Finding Average Marks of Whole Section
Our second example is based on finding average marks for the whole section. Here, we take a dataset that includes several students and their marks in different exam segments. Some of them are absent from certain exams. We would like to find the average marks when it is not equal to absent text in the dataset. That means when it gets the absent text in the dataset, it will ignore them and find the average for the whole section. To understand the example, follow the steps
Steps
- First, we would like to find the average excluding the absent text.
- Select cell C13.
- Then, write down the following formula.
=AVERAGEIFS(C5:E11, C5:E11, "<>Absent")
- Press Enter to apply the formula.
🔎 Breakdown of the Formula
AVERAGEIFS(C5:E11, C5:E11, “<>Absent”): The AVERAGEIFS function takes the average range and criteria range and returns the required average. Here, the average range is C5 to E11, and our criteria range is C5 to E11 when it is not equal to Absent text. So, the AVERAGEIFS function takes this range and searches the given criteria. Finally, it returns the required average for the whole section excluding the Absent text.
3. Using AVERAGEIFS with Multiple Criteria
Our third method is based on using the AVERAGEIFS with multiple criteria. Here, we use two different criteria and find out the average for other available data. We take a dataset that includes sales rep., item, region, quantity, unit price, and total amount. We would like to find out the average excluding the north and south regions. To understand the method, follow the steps.
Steps
- First, we would like to select cell C13.
- Then, write down the following formula.
=AVERAGEIFS(G5:G11,D5:D11,"<>North",D5:D11,"<>South")
- Press Enter to apply the formula.
🔎 Breakdown of the Formula
AVERAGEIFS(G5:G11,D5:D11,”<>North”,D5:D11,”<>South”): The AVERAGEIFS function takes the average range and criteria range and returns the required average. Here, the average range is G5 to G11, and our criteria range is D5 to D11. When the range of cells D5 to D11 is not equal to the north and not equal to the south, it will return the average of the given range of cells. That means they will return the average for the east region.
Read More: Excel AVERAGEIFS with Multiple Criteria in Same Range
How to Use AVERAGEIFS If Value Is Not Equal to Blank in Excel
We can easily utilize the AVERAGEIFS function when the value is not equal to blank. In that case, the function ignores the blank cell and finds out the average for other available cells. To show this, we take two different examples including a single column and multiple columns.
Example 1: Using AVERAGEIFS for Single Column
For a single column, we would like to use the AVERAGEIFS function and find out the average value when the values are not equal to blank. In this single column, they will search the blank cells and ignore them. Finally, find out the average from the available data. Follow the steps.
Steps
- First, select cell C13.
- Then, write down the following formula.
=AVERAGEIFS(C5:C11, C5:C11, "<>")
- Press Enter to apply the formula.
- Then, drag the Fill Handle icon up to cell E13.
🔎 Breakdown of the Formula
AVERAGEIFS(C5:C11, C5:C11, “<>”): The AVERAGEIFS function takes the average range and criteria range and returns the required average. Here, the average range is C5 to C11, and our criteria range is C5 to C11 when it is not equal to blank. So, the AVERAGEIFS function takes this range and searches the given criteria. Finally, it returns the required average excluding the blanks.
Example 2: Using AVERAGEIFS for Multiple Columns
Our next example is based on a situation when we have blanks in multiple columns. The AVERAGEIFS function will take these criteria and ignore the entire row if there is a blank. Finally, find out the average of the given range of cells. We take a dataset that includes some students, subjects, and marks. Here, we have some blank cells present in the first two columns. We would like to calculate the average of the marks column after ignoring the blank rows. To understand the process, follow the steps.
Steps
- First, select cell C13.
- Then, write down the following formula.
=AVERAGEIFS(D5:D11,B5:B11,"<>",C5:C11,"<>")
- Press Enter to apply the formula.
🔎 Breakdown of the Formula
AVERAGEIFS(D5:D11,B5:B11,”<>”,C5:C11,”<>”): The AVERAGEIFS function takes the average range and criteria range and returns the required average. Here, we take two criteria. When the range of cells B5 to B11 is not equal to blank and also the range of cells C5 to C11 is not equal to blank, then, the function ignores those blanks and finds out the required average from the range cells D5 to D11. One thing you must remember is that when it finds a blank, it will ignore the whole row.
Read More: AVERAGEIFS for Multiple Criteria in Different Columns in Excel
Things to Remember
- If a cell in the given criteria range is blank, the AVERAGEIFS function will count as zero.
- If the cells don’t meet the criteria, the AVERAGEIFS function shows the #DIV/0! error value.
- If the range of finding average is a blank or text value, the AVERAGEIFS function shows the #DIV0! error value.
Download Practice Workbook
Download the practice workbook below.
Conclusion
We have shown three different examples of using the AVERAGEIFS not equal to a certain condition in Excel. We have also included the use of the AVERAGEIFS function when the value is not equal to blank. I hope we covered all the possible areas of this topic. If you have any questions, feel free to ask in the comment box.
Related Articles
- 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
- Difference Between AVERAGEIF and AVERAGEIFS in Excel
- How to Use AVERAGEIFS Function for Multiple Columns
<< Go Back to Excel AVERAGEIFS Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
