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.

## How to Calculate Average If Cell Is Not Blank in Excel: 2 Suitable Ways

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

### 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.

## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

## 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.

## 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**