Using SUM with REGEX or LEFT

[Huillam]

I’m using version 25.2.2.2

For example:
A1 = 123
A2 = 456
A3 = 789

I was wondering if there was an easy way to sum 1, 4 and 7 here.
I can extract the number using either VALUE(REGEX($A$1,"[:digit:]")) or VALUE(LEFT($A$1,1)).
Both SUM(VALUE(REGEX($A$1,"[:digit:]"))) and SUM(VALUE(LEFT($A$1,1))) return 1.
Both SUM(VALUE(REGEX($A$1,"[:digit:]")):VALUE(REGEX($A$3,"[:digit:]"))) and SUM(VALUE(LEFT($A$1,1)):VALUE(LEFT($A$3,1))) return an error 502.

For SUM(VALUE(REGEX($A$1:$A$3,"[:digit:]"))) and SUM(VALUE(LEFT($A$1:$A$3,1))) things are different (and I guess it’s a bug). In rows 1/2/3 their return 1/4/7, in another row their return #VALUE

Is it possible to mix both functions?

[Steph1]

To validate a formula like SUM(VALUE(LEFT($A$1:$A$3,1))), you need to press Ctrl+Shift+Enter to transform it into an array formula. The formula will appear within { and }.

(Note: if the formula above already is in a cell, then you need to add a space to “change” it, then press Ctrl+Shift+Enter. Editing a formula and pressing Ctrl+Shift+Enter directly does not transform it into an array formula).

[Huillam]

Oh that’s what I was missing. I tried to edit the formula and pressed Ctrl + Shift + Enter but it wasn’t working. Thank you very much.

[Steph1]

Indeed, I don’t know why you must modify the formula in order to take the Ctrl+Shift+Enter into consideration (or vice-versa, to go from an array formula to a normal formula).

[fpy]

@Steph1

know

mostly optimisation; but see all the gory details from @erAck comment in 158895 – UI: Ctrl-Shift-Enter does not create array formula if cell is unmodified
and tdf#38014

@Huillam

tried

you can also tick the Array check box in the Function Wizard,

or use SUMPRODUCT()