why not try this

edit replace
find what=~*
replce with
(blank)
click replace all.

in such case *0.2 which was a text becomes a number
when adding only the numbers are added and the strings like "trace" are
ignored.

in your method perhaps the numbers remain as text
if no other text is there the sum function takes the number - texts as
numbers
this is only my guess. mine is excel 2000


Kay wrote in message
...
I'm trying to sum a col of rainfall values that contain
- numerics
- numeric preceeded by an * e.g " *0.2 "
- text such as "Trace"

This works OK until "Trace" entered:
{ =SUM(IF(LEFT(D5:D35)="*",MID(D5:D35,2,LEN(D5:D35)) +0,(D5:D35)+0)) }
How can the above be amended to igno the entry of "Trace" (which is
included only for information, has no real value).

This is another solution someone offered a long time ago:
=SUM(N(D5:D35={"*0.2","*0.4","*0.6","*0.8","Trace" }))
It's returning an incorrect total though. How can this be modified to
sum correctly?

Thanks, Kaye