Akib Bin Rashid
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.
Download Practice Workbook
Download this workbook and practice while going through the article.
2 Easy Methods to Assign Value to Text and Sum in Excel
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.
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.
- Now, press ENTER. Excel will return the output.
- After that, use Fill Handle to AutoFill up to F13.
Read More: How to Use SUMIF with Text in Excel (9 Easy Ways)
Similar Readings
- How to Sum Only Numbers and Ignore Text in Same Cell in Excel
- Sum If Cell Contains Specific Text in Excel (6 Ways)
- How to Sum Cells with Text and Numbers in Excel (2 Easy Ways)
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)))))
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.
Read More: How to Sum Text Values Like Numbers in Excel (3 Methods)
Things to Remember
- Use absolute reference to lock a range
- Press CTRL+SHIFT+ENTER for an array formula
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. Please visit Exceldemy for more useful articles like this.
Very useful
Good job and God bless you
Hi G0DWIN,
Thank you for your kind words!