2

My question is how can I have digits of a cell added together in an array formula format, in that

12 | -> 3      //(result: 3 = 1 + 2)
14 | -> 5
46 | -> 10
11 | -> 2
53 | -> 8

And let’s just say the values 12, 14, 46 on are on column A and the answered would be B.

  • so the result will be 18? (3+5+10) ? – MARK MY ANSWER Jan 4 at 22:53
  • no, i am looking for a way to make 12 into 3 by making 12->1+2=3, via an array formula. The only thing that has worked for me is =REGEXREPLACE(A1:A,"\D+", "") however, that is only in a single row instance - changing it into =ArrayFormula(REGEXREPLACE(A1:A,"\D+", "")) does not calculate all values in the A column for me, but rather only the first one – user205439 Jan 4 at 23:00
0
  • up to 2 digits:

    =ARRAYFORMULA(IF(LEN(A1:A)=2, LEFT  (A1:A, 1)
                                + RIGHT (A1:A, 1), ))

  • up to 3 digits:

    =ARRAYFORMULA(IF(LEN(A1:A)=2, LEFT  (A1:A, 1)
                                + RIGHT (A1:A, 1),
                  IF(LEN(A1:A)=3, LEFT  (A1:A, 1)
                                + MID   (A1:A, 2, 1)
                                + RIGHT (A1:A, 1), )))

  • up to 4 digits:

    =ARRAYFORMULA(IF(LEN(A1:A)=2, LEFT  (A1:A, 1)
                                + RIGHT (A1:A, 1),
                  IF(LEN(A1:A)=3, LEFT  (A1:A, 1)
                                + MID   (A1:A, 2, 1)
                                + RIGHT (A1:A, 1),
                  IF(LEN(A1:A)=4, MID   (A1:A, 1, 1)
                                + MID   (A1:A, 2, 1)
                                + MID   (A1:A, 3, 1) 
                                + MID   (A1:A, 4, 1), ))))

  • added single digit support:

    =ARRAYFORMULA(IF(LEN(A1:A)=1,        A1:A,
                  IF(LEN(A1:A)=2, LEFT  (A1:A, 1)
                                + RIGHT (A1:A, 1),
                  IF(LEN(A1:A)=3, LEFT  (A1:A, 1)
                                + MID   (A1:A, 2, 1)
                                + RIGHT (A1:A, 1),
                  IF(LEN(A1:A)=4, MID   (A1:A, 1, 1)
                                + MID   (A1:A, 2, 1)
                                + MID   (A1:A, 3, 1) 
                                + MID   (A1:A, 4, 1), )))))

  • limit array for only first 3 ocurances:

    =ARRAY_CONSTRAIN(ARRAYFORMULA(IF(LEN(A1:A)=1,        A1:A,
                                  IF(LEN(A1:A)=2, LEFT  (A1:A, 1)
                                                + RIGHT (A1:A, 1),
                                  IF(LEN(A1:A)=3, LEFT  (A1:A, 1)
                                                + MID   (A1:A, 2, 1)
                                                + RIGHT (A1:A, 1),
                                  IF(LEN(A1:A)=4, MID   (A1:A, 1, 1)
                                                + MID   (A1:A, 2, 1)
                                                + MID   (A1:A, 3, 1) 
                                                + MID   (A1:A, 4, 1), ))))), 3, 1)

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.