Try this modification to your 1st formula:=20
(added another IF condition to ignore "Trace")

=3DSUM(IF(LEFT(D5:D35)=3D"*",MID(D5:D35,2,LEN(D5:D 35))+0,IF
(TRIM(D5:D35)=3D"Trace","",(D5:D35)+0)))

Above formula is array-entered with
CTRL+SHIFT+ENTER, instead of just ENTER

--
Rgds
Max
xl 97
---
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom

"Kay" wrote:
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:
{ =3DSUM(IF(LEFT(D5:D35)=3D"*",MID(D5:D35,2,LEN(D5:D 35))+0,
(D5:D35)+0)) }
How can the above be amended to igno the entry of "Trace"=20
(which is
included only for information, has no real value).=20

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

Thanks, Kaye