Mrinmoy Roy
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.
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}.
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: 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.
❸ 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 Use SUMIF with Text in Excel (9 Easy Ways)
Similar Readings
- How to Sum Names in Excel (4 Suitable Ways)
- How to Sum If Cell Contains Text in Another Cell 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.
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.
