How to Calculate Average If Criteria Not Blank/Ignore Blank Cell
In daily work we often need to calculate the average of some numbers based on given conditions or criteria. To calculate average with criteria, we can apply AVERAGEIF of AVERAGEIFS function. AVERAGEIFS function can handle more than one group of criteria range and criteria. In this article, we will show you how to calculate average of numbers whose corresponding cells in another group are not blank. Thus, we need to know some knowledge of AVERAGEIFS function.
In this article, we will let you know the syntax, arguments, and basic usage about AVERAGEIFS function, and apply it to build a formula, also let you know the calculation steps of the formula.
Table of Contents
EXAMPLE
Refer to “Score” column, some numbers are listed in range B2:B10. “Level” column is used for providing a level based on scores. In E2 and F2, we calculate the average of all scores and qualified scores separately. To calculate average of all scores, we can directly use AVERAGE function. To calculate average of qualified scores (cell is not blank in Level group), we need to add criteria to filter them, we can apply AVERAGEIFS function, it is also one of the most common used functions in Excel.
First, In C2, enter “=AVERAGE(Score)”. Then press Enter, AVERAGE function returns 73.33333333.
You can adjust decimal places by “Increase Decimal” or “Decrease Decimal” in “Number” section.
Keep clicking on “Decrease Decimal” button till decimal places is ok for you. Keep two decimal places in this case.
Now we can apply AVERGAEIFS function to calculate the average of qualified scores.
Before creating the formula, name range “B2:B10” to “Score”, “C2:C10” to “Level” in Name Box.
CREATE A FORMULA with AVERAGEIF FUNCTION
1. STEPS
Step 1: In F2, enter the formula =AVERAGEIFS(Score,Level,”<>”).
Step 2: Press Enter after typing the formula.
Only B2, B4, B5, B6, B8, B9 and B10 meet our given criteria “level is A or B or C”, so we calculate the average of numbers in above cells, total 7 numbers. (60+62+90+83+88+74+90)/7=78.14285714, keep two decimal places 78.14. The formula works correctly.
2.FUNCTION INTRODUCTION
AVERAGEIFS function is AVERAGE+IFS. It returns the average of some numbers in a range based on one or more given conditions or criteria.
Syntax:
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2], [criteria2],…)
It supports wildcards like asterisk ‘*’ and question mark ‘?’, also supports logical expressions like ‘>=’,’<=’. If wildcards or logical operators are required to build criteria, they should be enclosed into double quotes (““). In this case we entered “<>” to represent criteria.
3.ALL ARGUMENTS
AVERAGEIFS – Average Range
This example is very simple, we have only one average range A2:A10.
In the formula bar, select named range “Score”, press F9, values in this range are expanded in an array.
AVERAGEIF – Criteria Range
The criteria range is C2:C10.
In the formula bar, select named range “Level”, press F9, values in this range are expanded in an array.
AVERAGEIF – Criteria
The criteria in our case is “<>”, in this case it represents cells are not blank. As logical expression is used, so we add “” to quote “<>”.
4.EXPLANATION
After expanding values, the formula is displayed as:
=AVERAGEIFS({60;55;62;90;83;58;88;74;90},{"C";0;"C";"A";"B";0;"A";"C";"A"},"<>")
Note: 0 is recorded in this array to represent blank cells.
In the formula, the criteria is “<>” (“not equals to” operator), based on this criteria, cells without any level in “Level” column cannot meet our condition, so for the corresponding values in the average range, they are excluded in calculation.
{60;55;62;90;83;58;88;74;90} > {60;0;62;90;83;0;88;74;90} – Ignore blank cells
Now this new array only contains numbers. We can calculate the average now (60+62+90+83+88+74+90)/7=78.14.
SUMMARY
1. AVERAGE function is used for returning the average of some numbers in Excel.
2. AVERAGEIFS function returns the average of some numbers refer to one or more given condition or criteria.
3. AVERAGEIFS can support wildcards, logical expressions.
Related Functions

Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)…. 
Excel AVERAGEIF function
The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])…. 
Excel AVERAGEIFS function
The Excel AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….