Forum Discussion

philip369's avatar
philip369
Copper Contributor
Dec 05, 2019

Impossible to sum numbers in Excel

I hope someone can help with this because I am pulling my hair out.  When I try to sum numbers in a column, at the bottom of the page it gives me a count and not a sum.  When I try to Autosum, it g...
excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 09, 2024

philip369 

A different strategy would be to accept the values as text, potentially with embedded hard spaces, currency symbols, comma group separators etc, and use a Lambda function to strip out unwanted characters and put the pieces together once more as numbers.

"Worksheet formula"
= SUMTEXTλ(out) 

"where the function is:"
SUMTEXTλ = LAMBDA(text, 
    SUM(
        IFERROR(
            BYROW(text, LAMBDA(x,
                VALUE(CONCAT(REGEXEXTRACT(x, "(\d|\-|\.)+",1)))
            )),
        0)
    ));

IFERROR is intended to pick up the situation where there are no numbers in a text string.

Resources