Search code examples
google-sheets

ARRAYFORMULA SUM

I have a series of numbers in column A.

I want to get the digit sums in column B in a single ArrayFormula (if possible). 

1) REGEXEXTRACT(A1&"",REPT("(.)",LEN(A1))))
// returns a horizontal text array eg: 123 => 1|2|3

2) ARRAYFORMULA(VALUE(REGEXEXTRACT(A1&"",REPT("(.)",LEN(A1)))))
// returns the horizontal text array as a number array

3) SUM(ARRAYFORMULA(VALUE(REGEXEXTRACT(A1&"",REPT("(.)",LEN(A1))))))
// returns the digit sum of value in column A
  1. I cannot apply SUM() straight away to formula (1) as the returned array is text
  2. Using (3) or otherwise, is it possible to use a single ArrayFormula to get the Digit Sum of values in column A

Thank you.

EDIT: sample spreadsheet - currently, the formula is copied manually in column B - looking for a single ArrayFormula to do this.

Solution

  • Assuming the maximum number of digits per cell =7,

    =ARRAYFORMULA(MMULT(SPLIT(REGEXREPLACE(TEXT(A1:A2,REPT("0",7)),"\B","🈂"),"🈂"),ROW(A1:A7)^0))
    • Change ALL instances of 7 to the maximum number of digits per cell
    • TEXT to make constant the number of digits
    • REGEX to replace all \B with a arbitrary char
    • SPLIT the char to give a matrix
    • MMULT to multiply with a vertical 1 matrix to give the desired result.