How to **calculate average** of text in Excel is not a very commonly encountered scenario. However, we have to deal with various requirements our dataset demands. In this article, we demonstrate how to calculate the average of text in Excel using custom formulas.

Since we calculate the average of text depending on particular scenarios, we are not displaying any dataset as a sample dataset. You can use the below-mentioned formulas using your own dataset. Or you can download the dataset attached to this article.

**Table of Contents**hide

**Download Excel Workbook**

**Typical Average Functions in Excel Dealings with Texts**

Excel offers **AVERAGE**, **AVERAGEA**, **AVERAGEIF,** and **AVERAGEIFS** to simply calculate the average values of a range or array. Every time you see or hear the term **â€śAverageâ€ť**, these functions come to your mind. The syntaxes of these functions are

`AVERAGE (number1, [number2], ...)`

`AVERAGEA (value1, [value2], ...)`

`AVERAGEIF (range, criteria, [average_range])`

`AVERAGEIFS (avg_rng, range1, criteria1, [range2], [criteria2], ...)`

Among these, the **AVERAGE** function ignores **empty cells**, cells that contain **text** or **logical values**.

The outcomes of the **AVERAGE** function result in a **#DIV/0!** Error as it encounters no numeric values in the cells.

On the contrary, the **AVERAGEA** function includes the logical values **TRUE** and **FALSE**. Also allows texts in the entries but doesnâ€™t account for texts in its calculations.

The **AVERAGEA** function takes texts but doesnâ€™t include text values in the calculation. As a result, it displays **0** unable to find any numeric value to sum then average.

For the **AVERAGEIF** function, the** range** is anything with numbers or names, arrays, or references. And the **criteria** are anything with a number, expression, cell reference, or text. However, it averages the numeric values in the range that satisfy the imposed criteria. Similar arguments are applicable for the **AVERAGEIFS **function.

**Read More:** **How to Calculate Average of Multiple Ranges in Excel (3 Methods)**

**2 Easy Ways to Calculate Average of Text in Excel**

**Method 1: Using SUM and COUNTIF Functions to Calculate Average of Text in Excel**

Suppose in a scenario, we have to calculate the average number of *Product Name*s in the following dataset. That means what is the percentage of a particular product sold against the whole bunch of products.

To calculate the average, we have to first count the times of a particular product appearing in the entries. Then calculate its average using the simple average formula (i.e., **count/total count**).

**Step 1:** As you want to count the times of appearance, use the following formula in any adjacent blank cell (i.e., **E5**).

`=COUNTIF($C$5:$C$16,C5)`

The **COUNTIF** function has a syntax of **COUNTIF (range, criteria)**. The formula counts the criteria (i.e., **E5**) among all the *Product Name*s (i.e., **C5:C16**) and returns the occurred times. In the formula, we use absolute references to lock the range for each cell.

**Step 2:** Press **ENTER** and Drag the **Fill Handle** to display all the count numbers in the cells.

After applying the **Fill Handle** count numbers will appear in all cells. You can use the formula for counting any text appearances in a dataset.

**Step 3:** Now, since we have an individual occurrence number of each product, we can calculate the average of texts. Type the below formula in any adjacent cells

`=E5/SUM($E$5:$E$16)`

The formula divides the count numbers by the total count of products that appeared in the dataset.

**Step 4:** Hit **ENTER** then Drag the **Fill Handle**. As a result, the average values of *Product Name*s appear in all cells as shown in the picture below.

**Read More:** **How to Get Average Time in Excel (3 Examples)**

**Similar Readings**

**How to Calculate 5 Star Rating Average in Excel (3 Easy Ways)****Average Attendance Formula in Excel (5 Ways)****How to Determine Triple Exponential Moving Average in Excel****Generate Moving Average in Excel Chart (4 Methods)****Running Average: How to Calculate Using Excelâ€™s Average(â€¦) Function**

**Method 2: Using a Combined Formula While Assigning Value to the Text**

Now, in this case, we have **10** *Product*s along with their reviews. Letâ€™s say we pick those product reviews and want to calculate the average of text by assigning review texts a value. The below screenshot may convey the premise we want to articulate.

**Step 1:** Write the following formula in any blank cell (i.e., **H5**).

`=SUM(INDEX($L$5:$L$9,N(IF(1,MATCH(C5:G5,$J$5:$J$9,0)))))`

The formula consists of multiple functions such as **SUM**, **MATCH**,** IF**,** N**, and** INDEX**.

Firstly, the **IF** function performs a **logical_test** (i.e., **1**) and applies the **MATCH** function in case of **TRUE **outcomes. The **MATCH** function assigns values depending on the** lookup_value** matchings. For instance, it matches **Excellent** to point** 5** and **Bad** to point **1**.

Secondly, the **MATCH** function takes the reviews (**C5:G5**) as **lookup_value** and matches to an array (**$J$5:$J$9**) for an absolute match.

The **N** function converts a value into a number. In this formula, the resultant number passes to the **INDEX** function as the **row_num**. Then the **SUM** function combines all the array points in cell **H5**.

**Step 2:** The custom formula used in **Step 1** is an array formula. You have to press **CTRL+SHIFT+ENTER** altogether to display the sum. Afterward, Drag the **Fill Handle** to appear all the sum in other cells depicted in the following image.

**Step 3:** After getting the total sum points against reviews, apply the typical average formula in the adjacent cell (i.e., **I5**).

`=H5/SUM($H$5:$H$14)`

This formula calculates the average of each product by dividing it by total points.

**Step 4:** Use **ENTER** to apply the formula in cells then Drag the **Fill Handle** to make all the averages visible similar to the image below.

You can use any text then assign values or points in order to calculate their averages. Assigning values to texts gives a relative understanding of how a product is performing compared to others.

**Read More:** **Moving Average Formula in Excel (8 Uses with Examples)**

**Conclusion**

In this article, we calculate the average of text in Excel. We use the **SUM** and **COUNTIF** functions or array formulas to count the occurrences of particular texts. After we get the individual count of each product, we can use a typical average operation (i.e., **count/total count**) to calculate the average of text. Hope our datasets, formulas carry lucid insides of calculating the average of texts and able you to apply those in your instances. Comment, if you have further inquiries or have something to add.

**Related Articles**

**How to Calculate Average in Excel Excluding 0 (2 Methods)****Calculate Average Percentage Change in Excel (3 Simple Ways)****How to Calculate Class Average in Excel (6 Easiest Methods)****Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)****[Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)****How to Calculate 7 Day Moving Average in Excel (4 Ways)****Calculate Average Percentage of Marks in Excel (Top 4 Methods)****How to Calculate Average Rating in Excel (7 Methods)**