only total cells with $ amounts

J

Jeffery

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
 
P

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
 
S

Sloth

=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.
 
D

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.
 
Top