### Method 1 – Using SUM, INDEX, MATCH, and IF Functions to SUM Text Values Like Numbers

We have a list of names who are assigned to 4 codes individually.

Each of the assigned codes is tied to some numbers. There are 3 different codes. Each of their values is given below:

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

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

- Type the following formula in cell
**G5**. Here,**G5**is the topmost cell of the column:

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

- 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, press**Ctrl + Shift + Enter.**

**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:

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

- Drag down the
**Fill Handle**in cell**G5**to copy down the formula to the entire**Total**column.

Here’s the result.

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

### Method 2 – SUM Text Values Like Numbers Using the COUNTIF Function

In the following scenario, we have a list of names with their corresponding countries. These country names are all in text format. We will sum up the total occurrences of each of the country names.

- Make a list of countries from E5 to E7.
- Insert the following formula in cell
**F5**.

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

- Press the
**Enter**button.

- Drag the
**Fill Handle**from cell**F5**to**F7**to copy down the formula.

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

### Method 3 – Combining SUMPRODUCT and ISTEXT Functions to SUM Text Values

We have a column of texts and numbers and want to ignore all the numbers and get the number of text values.

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

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

- 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 the Practice Workbook**

**Related Articles**

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