Forum Discussion
There's a problem with this formula.
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?
- 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"))
12 Replies
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.
PeterBartholomew1 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.
- 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"))
