We have some movies and the remarks of some critics. We have assigned some values to these remarks. With these values, we will calculate the total score of each movie.

### Method 1 – Merge SUM and COUNTIF Functions to Assign a Value to Text and Sum

**Steps**:

- Go to
**F5**and enter the following formula

`=SUM(COUNTIF(C5:E5,$H$5:$H$7)*$I$5:$I$7)`

For each cell in C5:E5, the formula counts the instances of text in the lookup range, then multiplies that with the respective result.

- Press
**Enter**. Excel will return the output.

**Note**: This is an array formula. If you are using earlier versions of

**Excel**, you must press

**Ctrl + Shift + Enter**instead of

**Enter**only.

- Use the
**Fill Handle**to**AutoFill**to**F13**.

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

### Method 2 – Combine SUM, INDEX, MATCH, N, and IF Functions to Assign Value to Text and Sum

**Steps**:

- Go to
**F5**and insert 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:

- Hit
**Enter.**

- Use the
**Fill Handle**to**AutoFill**to**F13**.

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

**Download the Practice Workbook**

**Related Articles**

**<< Go Back to Excel Sum If Cell Contains Text** **|** **Excel SUMIF Function** **| ****Excel Functions ****|**** Learn Excel**

Very useful

Good job and God bless you

Hi G0DWIN,

Thank you for your kind words!