How To Sum Comma Separated Values In Excel

 SUM(FILTERXML("<a><b>" & SUBSTITUTE(cell,",","</b><b>") & "</b></a>","//b"))

This formula calculates the sum of comma-separated numbers in a cell in Excel using the FILTERXML function. Here's how it works:

1. SUBSTITUTE function replaces all commas in the selected cell (referred to as "cell" in the formula) with the closing and opening tags of a "b" element in XML format. This transforms the comma-separated values into a well-formed XML string. For example, if cell A1 contains "1,2,3", then the SUBSTITUTE function will convert it to "<b>1</b><b>2</b><b>3</b>".
2. The CONCATENATE function combines the XML string with the "<a>" opening and "</a>" closing tags to create a complete XML document. The resulting string will look like "<a><b>1</b><b>2</b><b>3</b></a>".
3. The FILTERXML function then extracts all the values of the "b" elements using an XPath expression that specifies the path to the "b" elements. In this case, the XPath expression is "//b", which means "select all 'b' elements at any level in the XML document".
4. Finally, the SUM function adds up all the numbers returned by FILTERXML to calculate the sum of the comma-separated values in the cell.

So, the entire formula works by converting the comma-separated values in a cell into an XML document, extracting the numbers using an XPath expression, and then summing the numbers.