Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Sum Text Values Like Numbers in Excel (3 Methods)

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.


Download Practice Workbook

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


3 Methods to Sum Text Values Like Numbers in Excel

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.

Sum Text Values Like Numbers in Excel

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 return {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}.

Formula to Sum Text Values Like Numbers

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)))))

Alternative Formula to Sum Text Values Like Numbers

❸ 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: Sum If a Cell Contains Text in Excel (6 Suitable Formulas)


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.

Using COUNTIF function to Sum Text Values Like Numbers in Excel

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

Autofill to Sum Text Values of Country Like Numbers

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 Use SUMIF with Text in Excel (9 Easy Ways)


Similar Readings


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.

Excel SUMPRODUCT and ISTEXT to Sum Text Values Like Numbers

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.

Read More: How to Sum Cells with Text and Numbers in Excel (2 Easy Ways)


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.


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. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo