Forum Discussion
There's a problem with this formula.
- Jan 12, 2021One other way I believe you can do it. Assuming your comma delimited text string of numbers is in cell A1:
=SUMPRODUCT(FILTERXML("<L><I>"&SUBSTITUTE(A1,",","</I><I>")&"</I></L>","//I"))
Hi Sergei
I had used the problem to challenge my thinking using the newly available LAMBDA functions.
I did also create a recursive formula, based upon a rework of the Fibonacci recursion spreadsheet. There must be times when I irritate members with my alien solutions and I felt that a recursive solution was a couple of steps too far here. However, to prove it exists
= TotalCSV(ItemCosts)
where TotalCSV refers to
= LAMBDA(residual,
LET(
n, IFERROR( FIND(",", residual), 1+LEN(residual)),
item, VALUE(LEFT(residual, n-1)),
reduced, REPLACE(residual, 1, n+1,""),
partialSum, IF(reduced<>"", TotalCSV(reduced), 0),
partialSum + item )
)(ItemCosts)I offer apologies for those who will not be in a position to evaluate such methods, and may not be for many years. We seem to have reached a point where Excel 365, though it can run legacy spreadsheet solutions, is actually an utterly different platform which supports a programming approach to solving problems. When and whether the two strategies will ever come together once more, I am not so sure.
PeterBartholomew1 , will check, thank you. But I'd generalize using any text as delimiter, e.g. like "=@="