< Go Back

Learn How to Sum Text Values Like Numbers in Excel

If you have a list or table of either ALPHABETS or TEXTS, it is possible to SUM the TEXT values once we have ASSIGNED NUMBERS to the TEXT or ALPHABET as the case may apply.

The advantage of this in excel is obvious. You can use it to evaluate the content of a digital questionnaire amongst others.

HOW TO SUM TEXT VALUES AS NUMBERS

To CONVERT TEXT values into their ASSIGNED NUMBERS, and then, SUM the result in excel, we can use the INDEX, MATCH, and SUM functions. As shown in the example and the curated steps below, the basic formula in Cell H5 is:

=SUM(INDEX(J5:J8,N(IF(1,MATCH(D5:G5,I5:I8,0)))))

How To Sum Or Add Text Or Alphabetical Values As Numbers

EXAMPLE

Step 1

Input your data as shown in the figure below. Assign a particular number for the ALPHABET or TEXT as the case may apply. We have four combined alphabets in two’s in figure 1 below; AG, UN, PN, and GN.

Figure 1- How To Sum Or Add Text Or Alphabetical Values Like Numbers

Step 2

Click on Cell H5 in figure 2. Then, click on the fx bar to input the following below. You can also copy and paste it.  This will work just fine.

=SUM(INDEX(J5:J8,N(IF(1,MATCH(D5:G5,I5:I8,0)))))

Figure 2- How To Sum Or Add Text Or Alphabetical Values Like Numbers

Step 3

Hold the CTRL+SHIFT BUTTONS and PRESS the ENTER BUTTON. After doing this, you should have the figure below which displays the answer we want, 25.

Figure 3- How To Sum Or Add Text Or Alphabetical Values Like Numbers

Now, let’s explain what we have done. The generic formula for this process is given below:

{=SUM(INDEX(Value,N(IF(1,MATCH(Code,0)))))}

The functions that enable the translation of the ALPHABETS or TEXTS to NUMBERS are the INDEX and MATCH functions. The N and IF functions are required to ensure that we get results for the entire row. A shorter stream of functions will result in producing values for just a set of alphabets or text.

In this example, in the place of VALUE, you will notice that the digits begin from Cell J5 and ends at J8. Hence, your value should look like this, J5:J8.

The CODE as shown on the figures begin from Cell I5 and ends at Cell I8. Hence, your code should look like this I5:I8.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar