How to Find Average If Cell Contains Text in Excel (4 Useful Ways)

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.

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.

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.

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

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

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.

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.

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

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.

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.

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.

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.

Related Articles

• Excel AVERAGEIF Function for Values Greater Than 0
• How to Calculate Average If Cell Is Not Blank in Excel
• How to Use Excel AVERAGEIF with Multiple Criteria