In this article, we will discuss two suitable ways to calculate an average of cells that are not blank.
Suppose we have a dataset that contains information about different types of Products and the quantity of each that has been ordered in different Months in columns C, D, and B respectively. We’ll calculate an average of the Quantity of those ordered products excluding the blank orders that appear in several months, using the AVERAGEIF, SUMIFS, and COUNTIFS functions.
Method 1 – Using the AVERAGEIF Function to Calculate the Average If a Cell Is Not Blank
First, we will apply the AVERAGEIF function to calculate the average if the cells are not blank in two cases: in a single column and in multiple columns.
1.1 – The AVERAGEIF Function in a Single Column
Let’s calculate the average of the cell values of the Quantity column only where the cell values in the Product column are not blank.
Steps:
- Merge cells E5 to E15. Then select the merged cells.
- Enter the following AVERAGEIF function in the Formula Bar:
=AVERAGEIF(C5:C15,"<>",D5:D15)Where:
- C5:C15 is the cell range of the function.
- “<>” is the criteria.
- D5:D15 is the average_range.
- Press Enter to get an average of 78.57.
- If we count the cells that have blanks, the average becomes 77.56.
Read More: How to Calculate Average If Number Matches Criteria in Excel
1.2 – Applying the AVERAGEIFS Function in Multiple Columns
Now we will calculate the average of the cell values of the Quantity column only where the cell values in the Product and Month columns are not blank.
Steps:
- Merge cells E5 to E15. Then select the merged cells.
- Enter the following AVERAGEIFS function in the Formula Bar:
=AVERAGEIFS(D5:D15,C5:C15,"<>",B5:B15,"<>")Where:
- D5:D15 is the cell average_range.
- C5:C15 is criteria_range1.
- “<>” is criteria1.
- B5:B15 is criteria_range2.
- “<>” is criteria2.
- Press Enter to return an average of the cells that are not blank of 79.33.
Read More: How to Use Excel AVERAGEIF with Multiple Criteria
Method 2 – Combining the SUMIFS and COUNTIFS Functions to Calculate the Average If Cells Are Not Blank
Last but not least, we will merge the SUMIFS and COUNTIFS functions to calculate the average of the non-blank cells.
Steps:
- Select cell E5.
- Enter the following formula:
=SUMIFS(D5:D15,D5:D15,">=0")/COUNTIFS(D5:D15,">=0")Formula Breakdown:
- Inside the SUMIFS function, the first D5:D15 is the sum_range.
- The second D5:D15 is the criteria_range.
- “>=0” is the criteria of the SUMIFS function.
- Inside the COUNTIFS function, D5:D15 is the criteria_range.
- “>=0” is the criteria.
- Press Enter.
The average if cells are not blank is 81.
Read More: Excel AVERAGEIF Function for Values Greater Than 0
Things to Remember
The AVERAGEIF Function returns a #DIV/0! error if any referenced cell is non-numeric.
Download Practice Workbook
Related Articles
- How to Find Average If Cell Contains Text in Excel
- Excel AVERAGEIF with ‘Greater Than’ and ‘Less Than’ Criteria
- How to Find Average If Values Lie Between Two Numbers in Excel
<< Go Back to Excel AVERAGEIF Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
