How to SUM() range of cell values as text with "$" character?-CodePudding

I want to SUM the values in a range of rows. The values are as text because these contain character $. It doesn't work simply with SUM so I tried to take the $ character away using ÀRRAYFORMULA together with SUBSTITUTE, or just SUBSTITUTE and then SUM like this:

=SUBSTITUTE(SUM(E2:E13),"$ ","")

and this:

=ARRAYFORMULA(SUBSTITUTE(SUM(E2:E13),"$ ",""))

But the result is always 0. Is there a way to do this? I know that if the cells are formatted as number a simple SUM will work but on my main file the output of those values come with $ character so I need to find a way to take out the $ and then sum in 1 formula.

Here is my test file: https://docs.google.com/spreadsheets/d/1YYLARGtXXIH1rDTa42hQtB0W-XXk4X4He7lLgev91Tc/edit?usp=sharing

CodePudding user response：

Try:

=arrayformula(sum(value(iferror(regexreplace(B2:B,"^\$\ ",),))))

CodePudding user response：

So I ended up using:

=SUM(ARRAYFORMULA(VALUE(SUBSTITUTE(B2:B13,"$ ",""))))

It is important to use VALUE to make the array go from text to number after $ is taken out by SUBSTITUTE. This is why the order is important:

SUBSTITUTE-> VALUE-> ARRAYFORMULA -> SUM