In this Article

*This tutorial will demonstrate how to find the sum of text values where a unique code is assigned to each such text value in Excel and Google Sheets.*

## SUM Numbers Stored as Text

First we will look at how to sum numbers stored or formatted as text.

In the following example, the column Salary is stored as text. If attempt to sum the values, Excel will display a zero.

1 |
=SUM(C3:C7) |

Instead, to perform the SUM Operation on numbers stored as text, you can use an array formula with the SUM and VALUE functions like this:

1 |
=SUM(VALUE(C3:C7)) |

The VALUE Function converts a text representing a number to a number. The SUM Function sums those numbers.

In Excel 365 and version of Excel newer than 2019, you can simply enter the formula like normal. However, when using Excel 2019 and earlier, you must enter the array formula by pressing **CTRL + SHIFT + ENTER** (instead of **ENTER**), telling Excel that the formula is an array formula. You’ll know it’s an array formula by the curly brackets that appear around the formula (see top image). In later versions of Excel and Excel 365, you can simply press **ENTER** instead.

Let us look at the following explanation to get a better understanding of the formula.

### VALUE Function

Used as an array formula, the VALUE Function converts the whole range of numbers stored as text to an array of numbers and returns it as an input for the SUM Function.

1 |
=SUM({10000;6500;7500;15000;8000}) |

To see what the VALUE Function returns, select the required function and press **F9**.

## SUM of Text Values

To SUM a range of text values where a unique code is assigned to each such text value, an array formula can be used.

The following table records what people select for three given questions. The table on the right lists the four possible answers each of which has been assigned a code value. We have to sum the codes for each person.

1 |
=SUM(INDEX(I$3:I$6,N(IF(1,MATCH(C4:E4,H$3:H$6,0))))) |

We will walk through the formula below.

### MATCH Function

The MATCH Function looks for a specified item in a range and returns its relative position in that range. Its syntax is:

Being an array formula in this example, let’s see what the MATCH Function returns.

1 |
=SUM(INDEX(I$3:I$6,N(IF(1,{1,3,2})))) |

For a given person, the MATCH Function finds the relative position of each answer in the range **H3:H6**. The result is an array of positions.

**Note:** In an array formula, to view what a function returns, select the required function and press **F9**.

### IF & N Function

The IF and the N Function used together return the following array as an input for the INDEX Function.

1 |
=SUM(INDEX(I$3:I$6,{1,3,2})) |

Here, the two functions return an array of relative position of answers in the range **H3:H6**. The purpose of using the IF and the N Functions is for performing a process called dereferencing. In simple terms, the two functions force the INDEX Function to pass on the whole array of code values to the SUM Function.

We explain this in the next section.

### INDEX Function

The INDEX function returns the value positioned at the intersection of a specified row and column in a range. Its syntax is:

Let’s see how it works as an array formula:

1 |
=SUM({10,5,8}) |

The INDEX Function finds the code values in the range **I3:I6** according to the given position numbers. It then returns an array of values, i.e. the respective code for each answer, to the SUM Function to perform operations.

Make sure the number of rows and columns in both the **Answer** and the **Code** column is same.

### SUM Function

The SUM Function will sum the code values returned by the INDEX Function.

1 |
=SUM({10,5,8}) |

All of this put together yields our initial formula:

1 |
{=SUM(INDEX(I$3:I$6,N(IF(1,MATCH(C4:E4,H$3:H$6,0)))))} |

## SUM of Text Values – Without IF & N Functions

This section explains how Excel responds if we don’t use the IF and the N Function in the above mentioned formula.

The same example is being used with the same codes and answers.

1 |
{=SUM(INDEX(I$3:I$6,MATCH(C4:E4,H$3:H$6,0)))} |

As you can see, the INDEX Function passes only the code for the first answer to the SUM Function. If you scrutinize on the INDEX Function by pressing **F9** you’ll get the following:

The #VALUE! Error is returned because the INDEX Function cannot read the array of row numbers as an array. Hence, using the IF & the N Function does the trick.

**Note: **In Excel 365, you can skip using the IF and the N Functions altogether.

## Sum Text– Google Sheets

These formulas work the same in Google Sheets as in Excel.