In this tutorial, I am going to show you 5 quick tricks to calculate the average only for cells with values in Excel. You can quickly use these methods even in large datasets to find the average for the data cells especially if there are some blank cells in them. Throughout this tutorial, you will also learn some important Excel tools and functions that will be very useful in any Excel-related task.

**Table of Contents**Expand

## How to Calculate Average Only for Cells with Values in Excel: 5 Quick Tricks

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 6 rows and 4 columns. Initially, we formatted all the cells containing dollar values in *Accounting* format. For all the datasets, we have 2 unique columns named *Item *and *Sales*. Although we may vary the number of columns later on if that is needed.

### 1. Applying AVERAGE Function

**The AVERAGE function** in Excel can calculate the arithmetic mean of the values that we provide as inputs. We can use this function to calculate the average of only the cells with some values.

**Steps:**

- First, go to cell
**D10**and insert the following formula.

`=AVERAGE(B5:E9)`

- Now, press
**Enter**and this will calculate the average of the total sales.

**Read More: **How to Exclude a Cell in Excel AVERAGE Formula

### 2. Utilizing AVERAGEA Function

**The AVERAGEA function** works similarly to the previous function except that it also considers texts, *TRUE* and *FALSE* values. Let us see how to use this function to calculate the average of cells only with values in Excel.

**Steps:**

- To begin with, double-click on cell
**D10**and enter the below formula.

`=AVERAGEA(C5:C9,E5:E9)`

- Next, press the
**Enter**key and you should get the average of only the cells with values.

**Read More:** How to Calculate Average of Multiple Columns in Excel

### 3. Average with Criteria by AVERAGEIF Function

**The AVERAGEIF function** in Excel requires a criteria range to work with, unlike the previous ones. We can use this criteria field to calculate the average of cells with values only.

**Steps:**

- To begin this method, double-click on cell
**D10**and insert the formula below.

`=AVERAGEIF(B5:E9,"<>0")`

- Next, press the
**Enter**key and this will find the*Sales Average*inside cell**D10**.

### 4. Using AVERAGEIFS Function

**The AVERAGEIFS function** in Excel can calculate the average of cells only with values using multiple criteria.

**Steps:**

- To start this method, navigate to cell
**D10**and type the following formula.

`=AVERAGEIFS(B5:E9,B5:E9,"<>0")`

- After that, press the
**Enter**key or click on any blank cell. - Immediately, this will give you the average for the cells that have some values.

### 5. Average Database with DAVERAGE Function

The **DAVERAGE **function in Excel works with databases and can find the average of cells only with values if we set the proper criteria. Let us see how we can use this function.

**Steps:**

- As previously, insert the below formula inside cell
**G6**.

`=DAVERAGE(B4:D10,"Price",F4:F5)`

- Finally, press the
**Enter**key and you should get the average price for the color*Red*.

## How to Calculate Average If Cells Contain Text in Excel

We can use the **AVERAGEIF **function in Excel to look for text inside cells and then count the average of them accordingly. Follow the steps below to apply this function.

**Steps:**

- First, go to cell
**E5**and insert the following formula.

`=AVERAGEIF(B5:B10,"*John*",C5:C10)`

- Next, press
**Enter**and this will find the average points for**John**as we set as the text contains criteria.

**Read More: **How to Find Average of Specific Cells in Excel

**Download Practice Workbook**

You can download the practice workbook from here.

## Conclusion

I hope that you were able to apply the methods that I showed in this tutorial to calculate the average only for cells with values in Excel. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, If you have any queries, please let me know in the comments.

## Related Articles

- How to Average a Column in Excel
- How to Average Every Nth Row in Excel
- How to Calculate Average of Multiple Ranges in Excel
- How to Average Only Visible Cells in Excel
- How to Find Average with Blank Cells in Excel
- How to Fix Divide by Zero Error for Average Calculation in Excel
- How to Ignore #N/A Error When Getting Average in Excel
- [Fixed!] AVERAGE Formula Not Working in Excel

**<< Go Back to Conditional Average****Â | **Calculate Average **| **How to Calculate in ExcelÂ | Learn Excel