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