In our daily lives, calculating the average of cells containing specific text is a typical chore, and this is where Microsoft Excel shines. In the following article, we’ll outline 4 approaches **how to find average if cell contains text in Excel**. Moreover, we’ll also go through how we can compute the average of cells that do not contain a particular text.

Now, let’s consider the **Sales and Profit based on the Location** dataset shown in the **B4:D14 **cells which shows the **Location**, **Sales**, and **Profit **in USD respectively. Henceforth, let us glance at each of the methods shown below with simple illustrations.

Here, we have used *Microsoft Excel 365* version, you may use any other version according to your convenience.

__Method-1__: Calculate the Average in Excel If Cell Contains Text (Single Criterion)

Let’s start with the simplest way to obtain the average based on a single text criterion. In simple terms, we want to enter the name of a *Location *and calculate the corresponding *Average Sales *made according to the dataset. Therefore, let’s take a close look at the following three sub-methods.

### 1.1 Using AVERAGEIF Function

Firstly, let’s familiarize ourselves with the **AVERAGEIF function** which computes the arithmetic mean of the cells specified under a given condition or criterion. Here, we’ve chosen *Chicago* as the *Criteria* and we want to obtain the *Average Sales*, so, just follow these steps.

📌 ** Steps**:

- At the very beginning go to the
**C18**cell >> enter the formula given below.

`=AVERAGEIF(B5:B14,C16,C5:C14)`

Here, the **B5:B14 **and **C5:C14** range of cells refer to the *Location *and *Sales *columns respectively and the **C16 **cell represents the *Criteria Chicago*.

Finally, the results should look like the image given below.

### 1.2 Use of AVERAGEIFS Function

The **AVERAGEIFS** **function** is similar to the **AVERAGEIF ** function with the added benefit that it handles **multiple criteria** from different rows and columns to return the average value. In this case, we’ve chosen only one *Criterion* which is Chicago, and we want to calculate the *Average Sales*, hence, just follow along.

📌 ** Steps**:

- Initially, move to the
**C18**cell >> type in the formula shown below.

`=AVERAGEIFS(C5:C14,B5:B14,C16)`

In the above expression, the **B5:B14 **and **C5:C14** range of cells indicate the *Location *and *Sales *columns whereas the **C16 **cell represents the *Criteria Chicago*.

**Formula Breakdown:**

**AVERAGEIFS(C5:C14,B5:B14,C16) →**finds average for the cells specified by a given set of conditions or criteria. Here,**C5:C14**is theargument which is the*average_range**Sales*column. Next,**B5:B14**is theargument which refers to the*criteria_range1**Location*column and the**C16**is theargument which is*criteria1**Chicago*.**Output → $1778**

Lastly, your output should look like the picture shown below.

### 1.3 Applying Excel VBA Code

Alternatively, you can also calculate the average based on a single text criterion using the **VBA Code** below. It’s simple & easy, just follow along.

📌 ** Steps**:

- First, navigate to the
**Developer**tab >> click the**Visual Basic**button.

Now, this opens the **Visual Basic Editor** in a new window.

- Second, go to the
**Insert**tab >> select**Module**.

For your ease of reference, you can copy the code from here and paste it into the window as shown below.

```
Sub Calculate_Average_with_Condition()
Dim wrksht As Worksheet
Set wrksht = Worksheets("VBA Code")
wrksht.Range("C18") = Application.WorksheetFunction. _
AverageIf(wrksht.Range("B5:B14"), wrksht.Range("C16"), _
wrksht.Range("D5:D14"))
End Sub
```

**⚡**** Code Breakdown:**

Now, I will explain the **VBA** code which is divided into 2 steps.

- In the first portion, the sub-routine is given a name, here it is
**Calculate_Average_with_Condition()**. - Next, define the variable
**wrksht**and assign the**“VBA Code”**worksheet to this variable.

- In the second potion, specify the cell where to return the result using the
**Range object**, in this case, the**C18**cell. - Now, use the
**WorksheetFunction object**to call the**AVERAGEIF**function. - Following this, specify the
argument which is the*range***B5:B14**range, theargument which is the*criteria***C16**cell, and finally, theargument which is the*average_range***D5:D14**range.

- Third, close the
**VBA**window >> click the**Macros**button.

This opens the **Macros** dialog box.

- Following this, select the
**Calculate_Average_with_Condition**macro >> hit the**Run**button.

Eventually, the results should look like the screenshot shown below.

**Read More:** **Excel AVERAGEIF with ‘Greater Than’ and ‘Less Than’ Criteria**

__Method-2__: Finding Average If Cell Contains Text (Multiple Text Criteria)

What if you want to specify more than one condition? In the following sub-methods, we’ll answer this exact question by selecting two text criteria to calculate the average value. So, let’s see it in action.

### 2.1 Using AVERAGE and AVERAGEIF Functions

In this sub-method, we’ll combine the **AVERAGE **and **AVERAGEIF **functions to compute the *Average Profit *for the given *Locations *of *Chicago *and *Utah*.

📌 ** Steps**:

- In the first place, navigate to the
**C19**cell and insert the following expression in the**Formula Bar**.

`=AVERAGE(AVERAGEIF(B5:B14,C16,D5:D14),AVERAGEIF(B5:B14,C17,D5:D14))`

In the above formula, the **B5:B14 **and **D5:D14** range of cells point to the *Location *and *Profit *columns while the **C16 and C17 **cells refer to the *Criteria *of *Chicago *and *Utah*.

**Formula Breakdown:**

**AVERAGEIF(B5:B14,C16,D5:D14) →**returns the arithmetic average for the cells specified by a given set of conditions or criteria. Here,**B5:B14**is theargument which is the*range**Location*column. Next,**C16**is theargument that refers to*criteria**Chicago*. Following this,**D5:D14**is theargument which refers to the*average_range**Profit*column.**Output → $740**

**AVERAGEIF(B5:B14,C17,D5:D14) →**In this expression, the**C16**cell is theargument that refers to*criteria**Utah*while the**B5:B14**and**D5:D14**ranges refer to the*Location*and*Profit*columns.**Output → $1183**

**AVERAGE(AVERAGEIF(B5:B14,C16,D5:D14), AVERAGEIF(B5:B14,C17,D5:D14))****→**becomes**AVERAGE(740, 1183) →**returns the average of the arguments. Here, the values of**740**and**1183**are summed and divided by**2**to return their respective average.**Output → $961**

Subsequently, the result should look like the image given below.

### 2.2 Combining Excel IF, ISNUMBER, and MATCH Functions

As another option, you can also utilize the **IF**, **ISNUMBER**, and **MATCH** functions to calculate the *Average Profit* using two conditions which are *Chicago *and Utah. Now, allow me to demonstrate the process in the steps below.

📌 ** Steps**:

- To begin with, jump to the
**C19**cell and enter the following expression.

`=AVERAGE(IF(ISNUMBER(MATCH(B5:B14,C16:C17, 0)), D5:D14))`

In this formula, the **B5:B14**, **C16:C17**, and **D5:D14** ranges represent the *Location*, *Criteria*, and *Profit*.

**Formula Breakdown:**

**MATCH(B5:B14,C16:C17, 0) →**returns the relative position of an item in an array matching the given value. Here,**B5:B14**is theargument which refers to the*lookup_value**Location*. Following,**C16:C17**represents theargument that is the value to be matched. Lastly,*lookup_array***0**is the optionalargument which indicates the*match_type***Exact match**criteria.IF(ISNUMBER(MATCH(B5:B14,C16:C17, 0)), D5:D14)**IF(ISNUMBER(MATCH(B5:B14,C16:C17, 0)), D5:D14**)**→**checks whether a condition is met and returns one value if**TRUE**and another value if**FALSE**. Here,**ISNUMBER(MATCH(B5:B14,C16:C17, 0))**is theargument which returns only the matched values from the*logical_test**Profit*column**D5:D14**(argument) otherwise it returns*value_if_true***blank**(argument).*value_if_false***AVERAGE(IF(ISNUMBER(MATCH(B5:B14,C16:C17, 0)), D5:D14))****→**becomes**AVERAGE(930, 550, 1263, 1102) →**returns the average of the arguments. Here, the values of**930, 550, 1263,**and**1102**are summed and divided by**4**to return their respective average.**Output → $961**

Consequently, your output should look like the picture given below.

**Read More:** **How to Find Average If Values Lie Between Two Numbers in Excel**

__Method-3__: Averaging Cells That Match Text Exactly

Another way to match text with the **AVERAGEIF **function involves using the **Wild Card Character**. For instance, we want to find the *Average Sales *in the *Location *which have the letters “**ta**” present in the name. Therefore, just follow the steps shown below.

📌 ** Steps**:

- To start, proceed to the
**C18**cell and type in the formula given below.

`=AVERAGEIF(B5:B14,"*ta*",C5:C14)`

In the above formula, the **B5:B14 **and **C5:C14** range of cells refer to the *Location *and *Sales *columns respectively while **“*ta*” **represents the *Criteria*. As a note, the **Asterisk**(*****) character before and after tells the function to look for the keyword within a string of text. In this case, it matches all instances of the *Locations Dakota* and *Utah*.

Consequently, your output should appear as the image given below.

**Read More:** **How to Calculate Average If Number Matches Criteria in Excel**

__Method-4__: Find the Average of Cells That Match Text Partially

In a similar manner, we can use the **Wild Card **characters to get the *Average Sales *for the *Location *which partially matches the letters “**ta**”. Hence, let’s see the procedure in detail.

📌 ** Steps**:

- First and foremost, go to the
**C18**cell and insert the expression given below.

`=AVERAGEIF(B5:B14,"*ta",C5:C14)`

Here, the **Asterisk**(*****) character before the keyword tells the function to look for those instances ending with **“ta”** which matches all the occurrences of *Location Dakota*.

Finally, the result should look like the screenshot given below.

## How to Calculate Average If Cell Does Not Contain Text in Excel

So far we’ve discussed how to find average if cell contains text in Excel, but you can also exclude certain strings of text using the **AVERAGEIF **function. In this instance, we want to exclude the text *Dakota *and calculate the *Average Sales *for the rest of the *Locations*. So, let’s see the process step-by-step.

📌 ** Steps**:

- Initially, copy and paste the following expression into the
**C18**cell as shown below.

`=AVERAGEIF(B5:B14,"<>*Dakota*",C5:C14)`

In the above formula, the **<>** character is the **Not Equal to** operator which instructs the function to exclude all the instances of the text *Dakota *when calculating the *Average Sales*. In this case, all of the *Locations *except *Dakota* are matched.

Eventually, the *Average Sales* should be equal to the value of **$2778**.

**Download Practice Workbook**

You can download the practice workbook from the link below.

## Conclusion

In this article, we’ve shown you 4 effective methods for how to find average if cell contains text in Excel. I suggest you read the full article carefully and apply the knowledge to your needs. You can also download our free workbook to practice. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.