**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.

**Table of Contents**hide

## 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.

**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 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}**

- Output:

**IF(1,MATCH(C5:E5,$H$5:$H$7,0))**- Output:
**{1,2,1}**

- Output:

**N(IF(1,MATCH(C5:E5,$H$5:$H$7,0)))**- Output:
**{1,2,1}**

- Output:

**INDEX($I$5:$I$7,N(IF(1,MATCH(C5:E5,$H$5:$H$7,0))))**- Output:
**{5,3,5}**

- Output:

**SUM(INDEX($I$5:$I$7,N(IF(1,MATCH(C5:E5,$H$5:$H$7,0)))))****SUM(5,3,5)**- Output:
**13**

- Output:

- 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!