Md. Abdur Rahim Rasel
Sometimes we may need to calculate the average of numbers from a column in Excel. Average means the mean value of the input numbers. When we want to calculate an average if the cell is not blank in Microsoft Excel, it can offer several formulas to do that. In this article, we will discuss two easy and suitable ways to calculate an average that Excel average if not blank cell with explanations.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
2 Suitable Ways to Calculate Average If Cell Is Not Blank in Excel
Let’s say, we have a dataset that contains information about different types of Products and their quantity that has been ordered in different Months are given in columns C, D, and B respectively. We’ll calculate an average of the Quantity of those ordered products excluding blank orders in several months in Excel. To do that, we can apply the AVERAGEIF, SUMIFS, and COUNTIFS functions. Here’s an overview of the dataset for today’s task.
1. Use AVERAGEIF Function to Calculate Average If Cell Is Not Blank
In this section, we will apply the AVERAGEIF function to calculate an average if the cells are not blank in Excel. This is an easy and time-saving task. From our dataset, we can easily do that. We will calculate an average if the cells are not blank using the AVERAGEIF function for two cases. One for a single column and another for multiple columns. Have a look below at the sub-methods to learn.
1.1 Insert AVERAGEIF Function for Single Column
In this sub-method, we will calculate an average of the cell values of the Quantity column only where the cell values in the Product column are not blank. Let’s follow the instructions below to learn!
Steps:
- First of all, merge cells E5 to E15. Then select the merged cells.
- Further, type the AVERAGEIF function in the Formula Bar. The AVERAGEIF function in the Formula Bar is,
=AVERAGEIF(C5:C15,"<>",D5:D15)- Where C5:C15 is the cell range of the function.
- “<>” is the criteria of the AVERAGEIF function.
- D5:D15 is the average_range of the AVERAGEIF function.
- Hence, simply press Enter on your keyboard, and you will get an average if cells are not blank as 78.57 which is the return of the AVERAGEIF function that has been given below screenshot.
- If we count the cells that have blank, the average becomes 66.27 which has been given below screenshot. From that screenshot, you will be able to understand the average of the cells are blank or not.
Read More: How to Calculate Average If Number Matches Criteria in Excel
1.2 Apply AVERAGEIFS Function for Multiple Columns
In this sub-method, we will calculate an average of the cell values of the Quantity column only where the cell values in the Product and Month columns are not blank. Let’s follow the instructions below to learn!
Steps:
- First of all, merge cells E5 to E15. Then select the merged cells.
- Further, type the AVERAGEIFS function in the Formula Bar. The AVERAGEIFS function in the Formula Bar is,
=AVERAGEIFS(D5:D15,C5:C15,"<>",B5:B15,"<>")- Where D5:D15 is the cell average_range of the AVERAGEIFS function.
- C5:C15 is the criteria_range1 of the AVERAGEIFS function.
- “<>” is the criteria1 of the AVERAGEIFS function.
- B5:B15 is the criteria_range2 of the AVERAGEIFS function.
- “<>” is the criteria2 of the AVERAGEIFS function.
- Hence, simply press Enter on your keyboard, and you will get an average if cells are not blank as 79.33 which is the return of the AVERAGEIFS function that has been given below screenshot.
Read More: How to Use Excel AVERAGEIF with Multiple Criteria (5 Examples)
2. Combine SUMIFS and COUNTIFS Functions to Calculate Average If Cell Is Not Blank
Last but not least, we will merge the SUMIFS and COUNTIFS functions to calculate an average if the cells are not blank. From our dataset, we can easily do that. Let’s follow the instructions below to learn.
Steps:
- First of all, select cell E5 for the convenience of our work.
- Write down the below functions in that cell.
=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 of the SUMIFS function.
- “>=0” is the criteria of the SUMIFS function.
- Inside the COUNTIFS function, D5:D15 is the criteria_range of the COUNTIFS function.
- “>=0” is the criteria of the COUNTIFS function.
- After that, simply press Enter on your keyboard. As a result, you will get an average if cells are not blank as 81 which is the return of the SUMIFS and COUNTIFS function that has been given below screenshot.
Read More: Excel AVERAGEIF Function for Values Greater Than 0
Things to Remember
👉 The AVERAGEIF Function return #DIV/0! error when the value of all cells became non-numeric.
Conclusion
I hope all of the suitable methods mentioned above to calculate an average if the cell is not blank will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
