# How to Calculate Average If Cell Contains Text in Google Sheets

To calculate the average of cells containing text in Google Sheets, you need to use a combination of the COUNTIF and SUMIF functions. Here's a step-by-step guide:

- Prepare your data in the Google Sheet. Make sure you have both the numerical values and text in the same column or row.
- Identify the range where the data is. For example, if your data is in cells A1 to A10, your range would be A1:A10.
- In an empty cell, type the following formula to count the number of cells containing text:

`=COUNTIF(range, "*")`

Replace `range`

with the range you identified in step 2. For example:

`=COUNTIF(A1:A10, "*")`

- In another empty cell, type the following formula to sum the numeric values in the same range:

`=SUMIF(range, "<>", "")`

Replace `range`

with the range you identified in step 2. For example:

`=SUMIF(A1:A10, "<>", "")`

- Finally, divide the sum (from step 4) by the count of cells containing text (from step 3) to get the average value. For example, if the result of step 3 is in cell B1 and the result of step 4 is in cell B2, the formula would be:

`=B2/B1`

Now you have calculated the average of cells containing text in Google Sheets.

## Example

Let's say you have the following data in cells A1 to A6:

```
10
20
apple
30
40
banana
```

Here's how to calculate the average of cells containing text:

- In cell B1, type the following formula to count the number of cells containing text:

`=COUNTIF(A1:A6, "*")`

The result will be 6.

- In cell B2, type the following formula to sum the numeric values in the same range:

`=SUMIF(A1:A6, "<>", "")`

The result will be 100.

- In cell B3, divide the sum (from step 2) by the count of cells containing text (from step 1) to get the average value. The formula would be:

`=B2/B1`

The result will be 16.67, which is the average of the numeric values in the range A1:A6.

Did you find this useful?