SOLVED

There's a problem with this formula.

Copper Contributor

I have a set of data that had a varying series of numbers separated by commas in a column of cells in Excel 2016.  I used search and replace to change the commas to plus signs and then concatenated an equal sign to that data in a new cell in an attempt to create a formula that added together the numbers without having to retype everything.  When I did that, even though the formula is simple (e.g., =12+987987+3452+7893) and appears correct, I get a message that says "There's a problem with this formula."  It won't calculate even when I format the data type to number or general.  How might I correct this error and get the formulas to resolve?

12 Replies

@wiscy 

Although the result of the substitutions may look like a formula, it is in fact still a text string that happens to contain many digits.  Options include

1. Using EVAL2 from Charles Williams's FastExcel library

2. Use a defined name containing the old Macro4 function EVALUATE

3. Write a UDF using VBA to access its EVALUATE function

4. Parse the string and convert each value into a number using the VALUE function, then sum them.

 

Following option 2, write a formula 

= EVAL

in the cell immediately to the right of your formula.  Go to Name Manager and create a new name EVAL which refers to 

=EVALUATE(Sheet1!D5)

where D5 is a relative reference to your cell.  The "=" can be inserted at the beginning of the  string but the formulas will still work without it.

@Peter Bartholomew  I can't complete creation of the formula in the attached image because I get the error message.  In addition, I don't see Evaluate as a function within Excel 2016.  I get a #NAME! error creating the formula, as indicated.

@wiscy 

You can only use evaluate within a defined name; it does not work on the grid.

best response confirmed by wiscy (Copper Contributor)
Solution
One 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"))

@JMB17  This formula worked perfectly.  Thanks!

Quite welcome. Glad to hear it worked for you.

@JMB17 

I experimented with the XML solution using modern Excel 365 functions.  First LET, which allows local names to be introduced in order to remove nested formulas.

= LET(
    XML, "<list><item>" &
         SUBSTITUTE(listCosts,",","</item><item>") &
         "</item></list>",
    list, FILTERXML(XML,"//item"),
    SUM(list) )

XML is the markup language version of the CSV string, list is it as separate items and, since all calculation is performed as array calculation in 365, SUM will do.

 

The next step is to wrap the LET with the newly developed LAMBDA function which allows a user to pass different parameters to the same function.

= LAMBDA(listCosts,
   LET( ...
  ... )
   )(ItemCosts)

Naming the function TotalXML, gives a final form for the function call of

= TotalXML(ItemCosts)

I did also write a recursive Lambda function TotalCSV, using only basic text handling functions, but this is not the place to describe the solution.

 

@Peter Bartholomew 

I'd do not rely on FILTERXML with functions like TEXTSPLIT. Try to split text like "a, b&c, d". Ampersand is not the only symbol with which it doesn't work, you may google for all of them.

 

With LET/LAMBDA you may build such function without FILTERXML, and it will be more reliable. 

 

@Peter Bartholomew 

 

I don't have office 365 on my home machine. And, even though it's on my work machine, not only are we in the slowest channel, but the IT department packages and deploys the updates themselves, so we're actually months behind the slowest channel.

 

Yes, I think a textsplit function, if they've released one, would be better. The issue of filterxml errors due to special characters doesn't appear to apply based on the OP's description of the data.

 

 

 

@Sergei Baklan 

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.

@JMB17 

I have nothing against FILTERXML in this concrete case, I'd only don't use it as TEXTSPLIT in general.

@Peter Bartholomew , will check, thank you. But I'd generalize using any text as delimiter, e.g. like "=@="

1 best response

Accepted Solutions
best response confirmed by wiscy (Copper Contributor)
Solution
One 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"))

View solution in original post