We can’t sum text values in Google Sheets or any other spreadsheet applications without assigning numeric values to the texts.

For example, I have a table with employee ratings in text. The texts used for the ratings are “Exceptional”, “Very Good”, “Good”, “Fair” and “Very Poor”.

Each of these text values has scores 5, 4, 3, 2, and 1 respectively.

Table # 1:

Let’s see how to assign scores to the above 5 types of text values and sum text values in Google Sheets. I am using Vlookup and Sum for these.

If you want to sum the text values like numbers in each row in Google Sheets, you can use the Vlookup + Sum copy-paste formula or a Vlookup + Mmult array formula.

The easiest method to sum text values based on assigned numbers in Google Sheets is by using the non-array Vlookup + Sum. So let me start with that.

Formula Logic and Spreadsheet Preparation

There are two steps in the formula. Understanding that will help you write the formula very easily. They are;

1. Assigning scores/numbers to text – Vlookup.
2. Sum text values – Sum(Vlookup).

To sum text values we must first assign scores to text. How to do that?

The Vlookup takes care of assigning numbers here scores to text values. For that, you need to create a table (called ‘range’ in Vlookup) first.

The range contains the text values in one column and their corresponding scores. See that table which will acts as the ‘range’ in Vlookup.

Table # 2:

Our task is to assign the scores in Table # 2 to the corresponding values in Table # 1 and get the sum in the Total column in Table # 1.

Non-Array Formula to Sum Text Values in Google Sheets

Vlookup to Assign Scores to Corresponding Text Values

As I have mentioned, the first step to sum text values similar to numbers is to assign numbers to text values.

The following Vlookup will do that.

=ArrayFormula(vlookup(D3:F3,$A$3:$B$7,2,0))

The Vlookup takes multiple search keys from D3:F3 and so I have used the function ArrayFormula with Vlookup.

The ‘range in Vlookup is $A$3:$B$7 and the index (output) column is column 2 in $A$3:$B$7.

The above Vlookup assigns scores 5 to the text “Exceptional”, 3 to “Good” and again 5 to “Exceptional”.

Sum Vlookup Assigned Scores

The Vlookup is in cell G3. Just use the Sum function to sum the numbers returned by Vlookup.

Then drag the formula down.

=ArrayFormula(SUM(vlookup(D3:F3,$A$3:$B$7,2,0)))

This way you can sum text values after assigning scores to it in Google Sheets.

Note: Thre may be a chance of #N/A! error with the above formula in case there is any typo/blank cell in Table # 1.

To prevent that include IFNA with the above formula. You must insert IFNA before the SUM.

=ArrayFormula(SUM(IFNA(vlookup(D3:F3,$A$3:$B$7,2,0))))

Array Formula to Sum Text Values in Google Sheets

With some tweaks, we can make the above formula as an array formula.

The first change is in the Vlookup. In the above example, the search key is from the array D3:F3. Make it D3:F12.

=ArrayFormula(vlookup(D3:F12,$A$3:$B$7,2,0))

Here also you must wrap the Vlookup formula output with IFNA to return 0 in case of #N/A! This is a must for MMULT to work properly.

=ArrayFormula(ifna(vlookup(D3:F12,$A$3:$B$7,2,0),0))

The MMULT is for returning the matrix product of two matrices, right?

Syntax: MMULT(matrix1, matrix2)

The above formula is ‘matrix1’. We don’t want to calculate the product of two matrices and we don’t actually have ‘matrix2’.

Let’s generate ‘matrix2’ by using the Sequence formula as below.

=ArrayFormula(sign(sequence(3,1)))

This formula would return the number 1 thrice in a column (in 3 rows). You can test it on your sheet.

The number of columns in ‘matrix1’ is 3. So as a standard, ‘matrix2’ must contain an equal number of rows. We have now compatible matrix sizes in MMULT.

If you have more columns in your Table # 1, change the Sequence formula accordingly.

Here is the array formula in cell G2 to sum text values like numbers in Google Sheets.

=ArrayFormula(mmult(ifna(vlookup(D3:F12,$A$3:$B$7,2,0),0),sequence(3,1)^0))

Want to use an open array, like D3:F instead of D3:F12?

Read this guide – Proper Use of MMULT in Infinite Rows in Google Sheets.

Thanks for the stay. Enjoy!