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

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

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

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

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

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.

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo