How to Assign Value to Text and Sum in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. Excel is very useful for research and analysis. While analyzing any questionnaire, especially a Likert scale, we need to assign values to texts and then do further calculations. We can do that easily in Excel. In this article, I will show you 2 methods in Excel to assign value to text and sum.


How to Assign Value to Text and Sum in Excel: 2 Easy Methods

This is the dataset for today’s article. We have some movies and the remarks of some critics. I have assigned some values to these remarks. With these values, I will calculate the total score of each movie.

excel assign value to text and sum


1. Merge SUM and COUNTIF Functions to Assign Value to Text and Sum

First of all, I will show the use of the COUNTIF and SUM functions to assign values to text and then calculate the sum. Let’s do it step by step.

Steps:

  • First of all, go to F5 and write down the following formula
=SUM(COUNTIF(C5:E5,$H$5:$H$7)*$I$5:$I$7)

Formula Explanation:

Here, Excel assigns,

  • 5 for Good
  • 3 for Average
  • 1 for Bad

Then, Excel calculates the sum using the values in the range C5:E5.

excel assign value to text and sum

  • Now, press ENTER. Excel will return the output.

excel assign value to text and sum

Note: This is an array formula. So, if you are using earlier versions of Excel, you must press CTRL+SHIFT+ENTER instead of ENTER only.
  • After that, use Fill Handle to AutoFill up to F13.

Read More: How to Sum Text Values Like Numbers in Excel 


2. Combine SUM, INDEX, MATCH, N, and IF Functions to Assign Value to Text and Sum

Now, I will show another method that will use a combination of SUM, INDEX, MATCH, N, and IF functions to assign value to texts and calculate the sum. The formula will be a bit complex. However, the formula breakdown will help you understand easily.

Steps:

  • Go to F5 and write down the following formula
=SUM(INDEX($I$5:$I$7,N(IF(1,MATCH(C5:E5,$H$5:$H$7,0)))))

excel assign value to text and sum

Formula Breakdown:

  • MATCH(C5:E5,$H$5:$H$7,0)
    • Output: {1,2,1}
  • IF(1,MATCH(C5:E5,$H$5:$H$7,0))
    • Output: {1,2,1}
  • N(IF(1,MATCH(C5:E5,$H$5:$H$7,0)))
    • Output: {1,2,1}
  • INDEX($I$5:$I$7,N(IF(1,MATCH(C5:E5,$H$5:$H$7,0))))
    • Output: {5,3,5}
  • SUM(INDEX($I$5:$I$7,N(IF(1,MATCH(C5:E5,$H$5:$H$7,0)))))
  • SUM(5,3,5)
    • Output: 13
  • Now, press ENTER to get the output.

  • After that, use Fill Handle to AutoFill up to F13.

excel assign value to text and sum

Read More: How to Sum Only Numbers and Ignore Text in Same Cell in Excel


Things to Remember

  • Use absolute reference to lock a range
  • Press CTRL+SHIFT+ENTER for an array formula

Download Practice Workbook

Download this workbook and practice while going through the article.


Conclusion

In this article, I have explained 2 methods in Excel to assign value to text and sum. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below.


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.
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

2 Comments
  1. Very useful
    Good job and God bless you

  2. Hi G0DWIN,
    Thank you for your kind words!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo