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”): T**he **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**