Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles Miscellaneous Learn How to Sum Text Values Like Numbers in Excel

Learn How to Sum Text Values Like Numbers in Excel

We can sum text values as numbers after we have ASSIGNED NUMBERS to the Values. We can do this with the INDEX, MATCH, and SUM functions. This is useful for the evaluation of a questionnaire. The steps below will walk you through the procedure.

Figure 1: How to Sum Text Values As Numbers

Setting up the Data

  • We will input the names of five students as shown in figure 2 into Column A from Cell A4 to Cell A8
  • We will input their replies to the questions in Cell B4 to Cell E8 as shown in figure 2
  • Cell F3 will be named as TOTAL VALUE
  • We will assign numbers to each of the replies as shown in Column B from Cell B10 to Cell B14. The numbers are in Cell C10 to Cell C14

Figure 2: Setting up the Data

General Formula

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

Explanation

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.

Sum Text Values As Numbers

We will sum the reply of Jude by doing the following:

  • We will click on Cell F4 and input the formula below:

=SUM(INDEX(C10:C14,N(IF(1,MATCH(B4:E4,B10:B14,0)))))

Figure 3: Sum Text Values As Numbers

  • We will press the following keys to get the result, CRTL+SHIFT+ENTER

Figure 4: Sum Text Values As Numbers

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
How can I automatically change different cells and the information they display if we select different option from a drop down list?
Solved by V. U. in 59 mins
I received help from someone else on this website who provided me with the code; '' =if(and(B1-A1>=VALUE(ʺ-00:05:00ʺ),B1-A1<=VALUE(ʺ00:05:00ʺ)),ʺMATCHʺ,ʺNO MATCHʺ) '' This does not work for me? I've tried changing the cells but still unsuccessful . I'd like IF A1 and B1 match, come back Match, If A1 and B1 do not match, unmatch If A1 and B1 match with a 5 minute tolerance then match. Please can you assist, and advise if I need to change the cells (A1 B1) to (AB1 AB2) What the method is?
Solved by M. L. in 60 mins
I'm looking to offset from =today(), for example: If cell A13=today(), display cell A20. If cell B13=today(), display cell B20. If cell C13=today(), display cell C20. I want to do this for 365 rows. Is there a simpler way of doing this than a huge nested IF statement?
Solved by K. H. in 34 mins
Need help with a formula, I'd like it to be something similar to '' =IF(AB2=AC2,"Matches","No Match") '' but instead of if they 100% match I want a 5% variable for example, if Cell A1 contains 12:00 and Cell B1 contains 11:59 I want it to class as matching.
Solved by Z. J. in 13 mins
i am using sum to work out balances imported from a csv. If there is no number in the blank column the sum does not work but if i enter 0 and then delete it it works. What is the work around on that? Not had this before
Solved by F. D. in 35 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc