We all love spreadsheets for their powerful calculation abilities. But what if we need to sum text values? In Google Sheets, it’s not possible to directly sum text values. However, fear not! There is a clever workaround that involves assigning numeric values to the text. Let’s dive into this cool trick!
Formula Logic and Spreadsheet Preparation
To sum text values, we need to follow two steps in our formula. First, we assign scores to the text values using the Vlookup function. Then, we sum the assigned scores using the Sum function.
Let’s start by creating a table that contains the text values and their corresponding scores. This table will act as our “range” in the Vlookup function. Here’s an example:
Our task is to assign the scores from this table to the corresponding values in another table, let’s call it “Table #1”. We want to sum these assigned scores in the “Total” column of “Table #1”.
Non-Array Formula to Sum Text Values in Google Sheets
To assign scores to the text values in “Table #1”, we’ll use the Vlookup function. Here’s the formula:
=ArrayFormula(vlookup(D3:F3,$A$3:$B$7,2,0))In this formula, we’re using an ArrayFormula with Vlookup because we want to apply the Vlookup function to multiple search keys (from D3:F3). The range in the Vlookup function is $A$3:$B$7, and the index (output) column is column 2 in that range.
Now that we have assigned scores to the text values, we can sum them using the Sum function. Here’s the formula:
=ArrayFormula(SUM(vlookup(D3:F3,$A$3:$B$7,2,0)))You can drag this formula down to apply it to the other rows in “Table #1”. This way, you’ll be able to sum text values after assigning scores to them in Google Sheets.
Note: There may be a chance of encountering an #N/A! error if there are any typos or blank cells in “Table #1”. To prevent this, you can include the IFNA function in the formula before the SUM function. The updated formula would be:
=ArrayFormula(SUM(IFNA(vlookup(D3:F3,$A$3:$B$7,2,0))))This will handle any errors and return a zero if there’s a mismatch in the tables.
Array Formula to Sum Text Values in Google Sheets
With a few tweaks, we can transform our previous formula into an array formula. The first change is in the Vlookup function, where we’ll use a larger array range (from D3:F12 instead of D3:F3):
=ArrayFormula(vlookup(D3:F12,$A$3:$B$7,2,0))To handle any errors, we wrap the Vlookup formula output with the IFNA function:
=ArrayFormula(ifna(vlookup(D3:F12,$A$3:$B$7,2,0),0))Next, we’ll use the MMULT function to return the matrix product of two matrices. In our case, we don’t actually have a second matrix, but we can generate it using the Sequence function. Here’s the formula for creating the second matrix:
=ArrayFormula(sign(sequence(3,1)))The number of columns in the first matrix (from “Table #1”) is 3. So, as a standard, the second matrix must have an equal number of rows. By using the Sequence function, we can generate a column of ones (in this case, 3 rows).
Now, we can use the MMULT function to calculate the sum of the assigned scores:
=ArrayFormula(mmult(ifna(vlookup(D3:F12,$A$3:$B$7,2,0),0),sequence(3,1)^0))This formula multiplies the two matrices and returns the sum of the assigned scores.
Want to use an open array, like D3:F instead of D3:F12? You can find a guide on the proper use of MMULT in infinite rows in Google Sheets to explore this option further.
Now that you know this handy trick, go ahead and impress your colleagues with your text value-summing skills in Google Sheets!
Thanks for joining us on this journey. For more helpful tips and tricks, head over to Crawlan.com.
Enjoy and happy calculating!
