I have a list of numbers, some of them have leading underscores, some of them don't.

Which is the best way of adding up these numbers?

Note: I tried this custom script formula which for some reason doesn"t work (only returns the first item passed in the range), and anyway I guess there should be an easier way just using native GoogleSheet formulas.

function sum_with_underscores(underscored_nums) {
    let nums = underscored_nums.map( x => String(x).replace("_", ""))
    return nums.reduce((pv, cv) => parseFloat(pv)   parseFloat(cv), 0);
    }

CodePudding user response：

=SUM(ARRAYFORMULA(VALUE(SUBSTITUTE(<your_range>, "_", ""))))

CodePudding user response：

Assuming you would like to achieve this result using a script instead of a formula, the code below worked for me. Make sure to flatten the array before manipulating the data. Hope this helps.

  let values = sheet.getDataRange().getValues() // returns array of arrays
  let nums = values.flat() // converts [[_12, 34.0], [99.0, _42]] to [_12, 34.0, 99.0, _42]

  nums = nums.map( n => {
     return typeof(n) === 'number' ? n : parseFloat(n.replace("_", ""));
    })

  let sum = nums.reduce((acc, num)=> acc   num, 0)

}