AVERAGEIFS Function with “Not Equal to” Criteria (3 Examples)

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.


Download Practice Workbook

Download the practice workbook below.


3 Suitable Examples to Use AVERAGEIFS Function with “Not Equal to” Criteria in Excel

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

Finding Average Marks of Each Exam to Use AVERAGEIFS Not Equal to

  • Press Enter to apply the formula.

  • After that, drag the Fill Handle icon up to cell E13.

Calculating Average Marks of Each Exam to Use AVERAGEIFS Not Equal to

🔎 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.

Read More: [Fixed!] How to Fix AVERAGEIFS Value (#VALUE!) Error in Excel


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

Finding Average Marks of Whole Section to Use AVERAGEIFS Not Equal to

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

Using AVERAGEIFS with Multiple Criteria

  • 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, "<>")

Use AVERAGEIFS If Value Not Equal to Blank in Excel for single column

  • Press Enter to apply the formula.

  • Then, drag the Fill Handle icon up to cell E13.

Utilize AVERAGEIFS If Value Not Equal to Blank in Excel for single column

🔎 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.

Read More: How to Use AVERAGEIFS Between Two Values in Excel


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

Use AVERAGEIFS If Value Not Equal to Blank in Excel for Multiple columns

  • Press Enter to apply the formula.

Apply AVERAGEIFS If Value Not Equal to Blank in Excel for Multiple columns

🔎 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.

Conclusion

We have shown three different examples to use 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. Don’t forget to visit our ExcelDemy page.


Related Articles

Durjoy Paul

Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo