To sum comma-separated values in Excel that are in a single cell, you can use the SUMCSV or FILTERXML functions.
This tutorial will show how to sum comma-separated numbers in the same cell. At first look, the task is not easy, but with the help of a user-defined function, you can do that in seconds.
How to sum numbers with commas in a single cell?
- Type the =SUMCSV(A1) formula.
- Press Enter.
- The formula returns the sum of the values.
SUMCSV is a user-defined function, and Excel does not contain it by default. However, you can implement it quickly by downloading our free UDF add-in and writing easy-to-understand formulas.
Example
In the example, the data set is in cell B3. The cell contains numbers, but the SUM function will not work since the numbers are separated.
Apply the formula in cell D3:
=SUMCSV(B3)
Under the hood, the function splits the input string into an array of values. Next, it loops through the array and sums all values.
Sum comma-separated values using FILTERXML
If you are unfamiliar with custom functions, here is a workaround that uses only three functions: SUM, FILTERXML, and SUBSTITUTE.
Formula:
=SUM(FILTERXML("<a><b>" & SUBSTITUTE(A1,",","</b><b>") & "</b></a>","//b"))Explanation: The SUBSTITUTE function replaces commas with the closing and opening tags, so the output will be ready to convert the text string into XML format.
=SUBSTITUTE(A1,",","</b><b>")CONCATENATE creates a string that wraps the XML tags around the text:
"<a><b>" & SUBSTITUTE(A1,",","</b><b>") & "</b></a>"With the help of the FILTERXML function, you can extract the numbers from the XML string. The first argument, XML, is the string mentioned above. Next, The second argument of the function is “//b,”. Finally, the formula extracts all “b” characters from the XML string.
=FILTERXML(xml, xpath)As a last step, the SUM function sum all extracted numbers from a cell that contains comma-separated numbers.
SUM comma-separated values using the VALUE function
Here is another way to sum comma-separated values. The disadvantage of this method is that you can combine many built-in functions.
Formula:
=SUM(VALUE(TRIM(MID(SUBSTITUTE(A1,",", REPT(" ", LEN(A1))), (ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*LEN(A1)+1, LEN(A1)))))The best way to understand complex formulas is to evaluate them from the inside out.
Explanation:
- The SUBSTITUTE function replaces all commas with spaces. The REPEAT function repeats a string of spaces based on the logic: (the original length of the string) – (number of commas)
- The MID function returns the number position and extracts it.
- TRIM removes unwanted spaces.
- The VALUE function converts the text values to numeric values.
- Finally, the SUM function sums the numbers.
