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

Get FREE Advanced Excel Exercises with Solutions!

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.

excel average if cell contains text

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.

Using AVERAGEIF Function

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

excel average if cell contains text with AVERAGEIF function


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 the average_range argument which is the Sales column. Next, B5:B14 is the criteria_range1 argument which refers to the Location column and the C16 is the criteria1 argument which is Chicago.
    • Output → $1778

Utilizing AVERAGEIFS Function

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

excel average if cell contains text using AVERAGEIFS function


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.

Applying VBA Code

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

  • Second, go to the Insert tab >> select Module.

Inserting 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

VBA Code

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 range argument which is the B5:B14 range, the criteria argument which is the C16 cell, and finally, the average_range argument which is the D5:D14 range.

VBA Code Explanation

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

Running Macro

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

Results with VBA

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 the range argument which is the Location column. Next, C16 is the criteria argument that refers to Chicago. Following this, D5:D14 is the average_range argument which refers to the Profit column.
    • Output → $740
  • AVERAGEIF(B5:B14,C17,D5:D14) → In this expression, the C16 cell is the criteria argument that refers to 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

Combining AVERAGE and AVERAGEIF Functions

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

excel average if cell contains text with multiple criteria


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 the lookup_value argument which refers to the Location. Following, C16:C17 represents the lookup_array argument that is the value to be matched. Lastly, 0 is the optional match_type argument which indicates the 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 the logical_test argument which returns only the matched values from the Profit column D5:D14 (value_if_true argument) otherwise it returns blank (value_if_false argument).
  • 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

Using IF, ISNUMBER, and MATCH Functions

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

excel average if cell contains text with two criteria

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.

Averaging Cells that Matches Text Exactly

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

excel average if cell contains text with exact match

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.

Computing Average of Cells That Matches Text Partially

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

excel average if cell contains text with partial match


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.

Calculating Average If Cell Does Not Contain Text in Excel

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

excel average if cell contains text excluding specific text


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

Eshrak Kader
Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo