only total cells with $ amounts

[Guest]

I am trying to figiure out how to only add cells in a column that contain a
dollar amount.
EXAMPLE:

$12.00
$11.00
14.00
20.00
$10.00

$33.00 Total
Please Help

 

[Pete_UK]

This array* formula will do it, assuming your data is in A1:A5:

=SUM(IF(LEFT(A1:A5,1)="$",VALUE(RIGHT(A1:A5,LEN(A1:A5)-1)),0))

Adjust the cell references to suit.

*As this is an array formula, then once you have typed it in (or
subsequently edit it) you must commit the formula with CTRL-SHIFT-ENTER
(CSE) instead of the usual ENTER. If you do this correctly then Excel
will wrap curly braces { } around the formula when viewed in the
formula bar - you must not type these yourself.

Hope this helps.

Pete

 

[Guest]

Pete... Thanks for the quick response, but I tried your formula and only get
a blank cell for result.
I put the test data below in cells A1:A:5 then copied and pasted your
formula into cell A:6 and comitted the formula with (CSE) the formula shows
the {} brackets but the cell result is blank.

Any Ideas??
Thanks

 

[Guest]

=SUMPRODUCT(--(LEFT(A1:A5,1)="$"),--(SUBSTITUTE(A1:A5,"$","")))

The cells must be formatted as text. You can check this by selecting the
cell and looking at the formula bar. If A1 ($12.00) looks like $12.00 in the
formula bar, then it is formatted as text. If A1 ($12.00) looks like 12 in
the formula bar, then it is formatted as currency. If you enter $12.00 in a
cell without first formatting it to text, then it will format the cell as
currency. To avoid this either format the cell as text before entering data,
or preced the entry with an apostrophe like this '$12.00

I'm assuming you are having trouble with Pete's formula because of this issue.

 

[David Biddulph]

Is your $ symbol part of a text entry in the cell, or part of the
formatting?
I think Pete was expecting the former, but your results suggest the latter,
so you may need to use VBA.