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.

**Table of Contents**hide

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