EXCEL FORMULA: Sum Values in Text String

[Mike De Salvo]

For notation purposes, I've converted numbers to text strings with * in front of the dollar figure to tie to a citation. The number * varies. How then do I sum all the numbers together with the *? For example:

***$500

**$50

$100

*$75

<tbody>
</tbody>

How would I sum the above to be $725, noting that those with * are text strings?

 

[Eric W]

Maybe:

=SUM(SUBSTITUTE(A1:A4,"*","")+0)

Confirmed with Control+Shift+Enter.

 

[Mike De Salvo]

Eric, close but not quite. The following formula worked perfectly:

=SUM(IFERROR(VALUE(SUBSTITUTE(A1:A4,"*","")),0))

 

[Dave Patton]

=SUMPRODUCT(SUBSTITUTE(A1:A4,"*","")+0)

This does not require entering as an array.