Sometimes you may need to deal with various texts that are tied to numbers. This is a complex situation to handle. You will need to apply sophisticated formulas to do so. However, you don’t need to worry about this at all. Because I will teach you 3 different methods to sum text values like numbers in Excel. So, without any further discussion, let’s dive straight into the topic.

**Table of Contents**Expand

## How to Sum Text Values Like Numbers in Excel: 3 Methods

### 1. Using SUM, INDEX, MATCH, & IF Functions to SUM Text Values Like Numbers

In the following scenario, you can see a list of names who are assigned to 4 codes individually.

Next, each of the assigned codes is tied to some numbers. In total, there are 3 different codes. Each of their values is given below:

**ATK = 2****UNP = 5****SETP = 7**

Now, I will write a formula to calculate the sum value of all of these codes for each person on the list.

To calculate text values like numbers,

❶ Type the following formula in cell **G5**. Here, **G5 **is the topmost cell of the column, Total. This formula is a combination of the **SUM**, **IF**, **INDEX **& **MATCH **functions.

`=SUM(INDEX($J$5:$J$7,N(IF(1,MATCH(C5:F5,$I$5:$I$7,0)))))`

Here, one important thing to keep in mind is that this is an array formula. So, you have to handle this formula differently.

❷ After that, press the **ENTER **button to insert the formula into the cell if you are using Excel for Office 365.

If you are using an older version of Excel, then press the **CTRL + SHIFT + ENTER** button to insert the formula into the cell.

**Formula Breakdown**

**MATCH(C5:F5,$I$5:$I$7,0):**It compares the codes between the ranges**C5:F5**and**$I$5:$I$7**. Here, the second range of writing uses the absolute cell reference because it’s fixed. It returns**{1,2,3,2}**. This means the codes in the range**C5:F5**are equal to the codes in the range**$I$5:$I$7**at the following sequence:**1,2,3,**&**2**.**IF(1,MATCH(C5:F5,$I$5:$I$7,0))**returns**{1,2,3,2}**. This means the**IF**function multiplies**1**with the output of**MATCH(C5:F5,$I$5:$I$7,0)**.**INDEX($J$5:$J$7,N(IF(1,MATCH(C5:F5,$I$5:$I$7,0)))):**Here the**INDEX**function extracts the values of each of the codes from the range**$J$5:$J$7**. It returns**{2,5,7,5}**.**SUM(INDEX($J$5:$J$7,N(IF(1,MATCH(C5:F5,$I$5:$I$7,0))))):**This part is equivalent to**SUM({2,5,7,5})**. So the**SUM**function adds up all the values in the array**{2,5,7,5}**.

Alternatively, you can use the following formula instead of the previous formula. This formula is a combination of the **SUMPRODUCT**, **IF**, **INDEX **& **MATCH **functions. This formula actually, replaces the **SUM **function of the previous formula with the **SUMPRODUCT **function. Both formulas will produce the same result.

`=SUMPRODUCT(INDEX($J$5:$J$7,N(IF(1,MATCH(C5:F5,$I$5:$I$7,0)))))`

❸ Now drag down the **Fill Handle** in cell **G5 **to copy down the formula to the entire **Total **column.

Finally, you will see that, you have successfully calculated the sum of the codes like numbers as in the picture below:

**Read More: **How to Sum Only Numbers and Ignore Text in Same Cell in Excel

### 2. SUM Text Values Like Numbers Using COUNTIF Function

In the following scenario, you can see a list of names with their corresponding countries. There you will find **3** countries in total which are the **USA**, **UK**, and **France**. These country names are all in text format. If you want to sum up the total occurrences of each of the country names, you have to sum up their occurrences as if they are numbers. You can easily do it, using the **COUNTIF **function.

Now follow the steps below, to sum up, the occurrences of the text values like numbers.

❶ First, insert the following formula in cell **F5**.

`=COUNTIF($C$5:$C$14,E5)`

Here, the range **$C$5:$C$14** contains all the country names. Then **E5 **refers to the country, the **USA **whose number of occurrences we want to count.

❷ Now press the **ENTER **button.

❸ After that, drag the **Fill Handle** from cell **F5 **to **F7 **to copy down the formula.

Finally, you will get the sum of the total number of occurrences of each of the country names in the column, **Count**.

**Read More: **How to Assign Value to Text and Sum in Excel

### 3. Combining SUMPRODUCT & ISTEXT Functions to SUM Text Values

Suppose, you have a column full of texts and numbers. All you want to do is ignore all the numbers and get the sum of the number of occurrences of all the texts. Well, if the situation is so, you can use the following steps.

❶ Insert the following formula in cell **F5**.

`=SUMPRODUCT(--(ISTEXT(C5:C14)))`

❷ After that, press the **ENTER **button.

This formula will sum up the number of occurrences of all the text values like numbers. In this particular case, the formula returns **10**. This means the column **Country **has a total of **10 **text values.

**Formula Breakdown**

**ISTEXT(C5:C14):**Here,**the ISTEXT function**varifies whether a value in the range**C5:C14**is text or not. If the value is a text then it returns**TRUE**. Otherwise, it returns**FALSE**.**SUMPRODUCT(–(ISTEXT(C5:C14))):**The**SUMPRODUCT**function adds up all the ones**(1s)**returned by the**ISTEXT function.**

## Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the topics discussed in this article.

**Download Practice Workbook**

You can download the Excel file from the following link and practice along with it.

## Conclusion

To sum up, I have discussed 3 methods to sum text values like numbers in Excel. Please don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP.

**Related Articles**

**<< Go Back to Excel Sum If Cell Contains Text** **|** **Excel SUMIF Function** **| ****Excel Functions ****|**** Learn Excel**