Google Sheets: How to Calculate Average If Cell Contains Text

You can use the following formula to calculate the average in Google Sheets only for the cells that contain a specific text:

```=AVERAGEIF(A2:A13,"*text*",B2:B13)
```

This particular formula will calculate the average of the values in the range B2:B13 only for the cells that contain “text” in the range A2:A13.

Note: The asterisks are wildcard characters that tell Google Sheets to ignore any text before or after a specific string.

The following example shows how to use this formula in practice.

Example: Calculate Average If Cell Contains Text

Suppose we have the following dataset that shows the points scored by 12 different basketball players:

We can use the following formula to calculate the average points scored by players on any team with “avs” in the team name:

`=AVERAGEIF(A2:A13,"*avs*",B2:B13)`

The following screenshot shows how to use this formula in practice:

The average points scored by players on a team with “avs” in the name is 22.75.

In this particular example, there are two team names with “avs” in the name: Mavs and Cavs.

We can manually calculated the average points scored by players on these teams:

Average Points Scored = (31 + 23 + 21 +16) / 4= 22.75.

This matches the value calculated by our formula.

Note: You can find the complete documentation for the AVERAGEIF function in Google Sheets here.

The following tutorials explain how to perform other common tasks in Google Sheets:

May 13, 2024
April 25, 2024
April 19, 2024

One Reply to “Google Sheets: How to Calculate Average If Cell Contains Text”

1. Matt says:

This might work for Excel (I don’t know), but doesn’t seem to work for Google Sheets. The documentation for averageif doesn’t have anything about strings containing values.