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

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

Great! Thankyou Max for your prompt reply.

Kaye Seeton


On Sun, 28 Nov 2004 22:50:45 -0800, "Max"
wrote:

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

=SUM(IF(LEFT(D5:D35)="*",MID(D5:D35,2,LEN(D5:D35) )+0,IF
(TRIM(D5:D35)="Trace","",(D5:D35)+0)))

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

"Kay" wrote:
Great! Thankyou Max for your prompt reply

You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
---

Hi!

Here's something a little shorter and it's not an array:

=SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE
(D5:D35,"trace","0"),"*","")))

Biff

-----Original Message-----
Great! Thankyou Max for your prompt reply.

Kaye Seeton


On Sun, 28 Nov 2004 22:50:45 -0800, "Max"

wrote:

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

=SUM(IF(LEFT(D5:D35)="*",MID(D5:D35,2,LEN(D5:D35 ))+0,IF
(TRIM(D5:D35)="Trace","",(D5:D35)+0)))

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

.

"Biff" wrote:
...
Here's something a little shorter
and it's not an array:
=3DSUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE
(D5:D35,"trace","0"),"*","")))

Think it's case sensitive, Biff
(unlike the longer, array formula <g)

Had to change "trace" to "Trace"=20
before it worked ..=20

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

....and doesn't seem to tolerate empty cells.

KL

"Max" wrote in message
...
"Biff" wrote:
...
Here's something a little shorter
and it's not an array:
=SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE
(D5:D35,"trace","0"),"*","")))

Think it's case sensitive, Biff
(unlike the longer, array formula <g)

Had to change "trace" to "Trace"
before it worked ..

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

Hi!

Think it's case sensitive, Biff
(unlike the longer, array formula <g)

If case and blank cells need to be considered:

=3DSUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE(LOWER
("0"&A1:A10),"trace",""),"*","")))

Still shorter, still not an array, and still has less=20
function calls! <g

Biff

-----Original Message-----
"Biff" wrote:
...
Here's something a little shorter
and it's not an array:
=3DSUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE
(D5:D35,"trace","0"),"*","")))

Think it's case sensitive, Biff
(unlike the longer, array formula <g)

Had to change "trace" to "Trace"=20
before it worked ..=20

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

.

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

Not really suitable as I want the figures with the * to remain in
front of the rainfall amount when the sheet is printed.

Anything with the * denotes the amount is from deposits from dew or
fog rather than normal rain.

Thanks for the suggestion though.

Cheers, K

On Mon, 29 Nov 2004 12:22:28 +0530, "R.VENKATARAMAN"
&&& wrote:

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

Hi

Another one (an array function too)
=SUM(IF(ISNUMBER(SUBSTITUTE(D5:D35,"*","")*1),SUBS TITUTE(D5:D35,"*","")*1,0)
)


--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)


"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

Tks Arvi !

KC

On Mon, 29 Nov 2004 10:23:41 +0200, "Arvi Laanemets"
wrote:

Hi

Another one (an array function too)
=SUM(IF(ISNUMBER(SUBSTITUTE(D5:D35,"*","")*1),SUB STITUTE(D5:D35,"*","")*1,0)
)