AVERAGEIFS Function with "Not Equal to" Criteria: 3 Examples

Method 1 – Finding Average Marks of Each Exam

Steps

We would like to find the average, excluding the absent text.
Select cell C13.
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.

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. The average range is C5 to C11, and our criteria range is C5 to C11 when it is not equal to Absent text. The AVERAGEIFS function takes this range and searches the given criteria. It returns the required average, excluding the Absent text.

Method 2 – Finding Average Marks of Whole Section

Steps

Find the average, excluding the absent text.
Select cell C13.
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. The average range is C5 to E11, and our criteria range is C5 to E11 when it is not equal to Absent text. 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.

Method 3 – Using AVERAGEIFS with Multiple Criteria

Steps

Select cell C13.
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. 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.

How to Use AVERAGEIFS If Value Is Not Equal to Blank in Excel

Method 1 – Using AVERAGEIFS for Single Column

Steps

Select cell C13.
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.

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. The average range is C5 to C11, and our criteria range is C5 to C11 when it is not equal to blank. The AVERAGEIFS function takes this range and searches the given criteria. It returns the required average excluding the blanks.

Method 2 – Using AVERAGEIFS for Multiple Columns

Steps

Select cell C13.
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. 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. When it finds a blank, it will ignore the whole row.

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.

AVERAGEIFS with Not Equal to Criteria.xlsx

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

Get FREE Advanced Excel Exercises with Solutions!