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:

1. Prepare your data in the Google Sheet. Make sure you have both the numerical values and text in the same column or row.
2. Identify the range where the data is. For example, if your data is in cells A1 to A10, your range would be A1:A10.
3. 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, "*")

4. 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, "<>", "")

5. 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:

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

=COUNTIF(A1:A6, "*")

The result will be 6.

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

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

The result will be 100.

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