To sum numbers with a text string, you can use a formula with the SUM and TEXTAFTER functions or the LET function.
How to sum numbers with text
- Select cell D3.
- Type =SUM(–TEXTAFTER(data,”,”))
- Press Enter.
- The formula will sum all the numeric values extracted after the delimiter.
Example
In this example, we have a dataset in range B3:B9 containing text values paired with numerical values, separated by a comma. The goal is to calculate the total of all the numerical values extracted from the text strings and then create a summary table showing the total for each unique text item.
First, the formula in cell D3 uses the TEXTAFTER function to extract the numbers after the comma and sums them to calculate the total. After that, the summary table in columns F and G aggregates the data by text item, displaying each unique name and its corresponding sum of numbers.
Formula:
=SUM(–TEXTAFTER(data,”,”))
The result looks great!
Explanation
Here’s a detailed breakdown of how the formula in cell D3 works:
TEXTAFTER(data, “,”): The TEXTAFTER function extracts text after a specified delimiter (in this case, a comma) from each entry in the data range (B3:B10). Data contains text values like “Apple, 50” and “Banana, 40”. TEXTAFTER isolates the numeric part after the comma in each cell by specifying the comma as the delimiter. For example: “Apple, 50”, TEXTAFTER extracts “50”. For “Banana, 40”, it extracts “40”. This part of the formula returns with an array of text values: {“50”; “40”; “20”; “100”; “90”; “80”; “40”; “70”}.
Using double-negative method (–): We use the double minus sign (–) to convert text values in the array to actual numbers. In Excel, text representing numbers (like “50”) does not automatically act as numeric values in calculations. The — operator forces Excel to interpret them as numbers. After applying –, the array becomes {50; 40; 20; 100; 90; 80; 40; 70}, with each element now a number.
SUM: The SUM function takes the array of numbers and calculates their total. In this example, SUM({50; 40; 20; 100; 90; 80; 40; 70}) results in 490.
Working with LAMBDA
Here is a formula based on LAMBDA:
LET Function: LET allows you to define variables for different parts of the formula. Here, we define variables a, b, and c for use within the formula.
“a” = TEXTBEFORE(data, “,”). TEXTBEFORE extracts the part of each entry in data that appears before the comma (“,”). Since data contains entries like “Apple, 50” and “Banana, 40”, TEXTBEFORE(data, “,”) isolates the text part (e.g., “Apple” or “Banana”). As a result, “a” becomes an array containing the text values: {“Apple”; “Banana”; “Melon”; “Banana”; “Kiwi”; “Apple”; “Banana”; “Apple”}.
“b” = –TEXTAFTER(data, “,”). TEXTAFTER(data, “,”) extracts the part of each entry in data that comes after the comma, capturing the numeric values as text (e.g., “50” or “40”). The double minus sign (–) coerces these text values into actual numbers, making them usable for calculations.
“c” = UNIQUE(a) UNIQUE removes duplicates from a, giving us a unique list of text values. For example, UNIQUE(a) yields {“Apple”; “Banana”; “Melon”; “Kiwi”}, which represents each distinct item in data.
HSTACK(c, BYROW(c, LAMBDA(x, SUM(–(a=x) * b)))): HSTACK horizontally stacks arrays, placing c (unique text values) in one column and the results of BYROW in the adjacent column.
Using BYROW with LAMBDA to sum matching values:
- BYROW(c, LAMBDA(x, SUM(–(a=x) * b)))
- BYROW processes each item in c (each unique text value) row by row.
- For each unique value x in c, the LAMBDA function checks if each entry in a matches x (e.g., “Apple”, “Banana”).
- –(a=x) creates an array of 1s and 0s, where 1 indicates a match, and 0 indicates no match.
- Multiplying this array by b effectively zeroes out the values in b where there’s no match, leaving only the values associated with each unique text in c.
- SUM(–(a=x) * b) then adds these values, getting the total for each unique text item.
Download the practice file
Download the Workbook and take a closer look at the formulas.