#1   Report Post  
Kay
 
Posts: n/a
Default Sumif Rainfall

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
  #2   Report Post  
Kay
 
Posts: n/a
Default

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


  #3   Report Post  
Kay
 
Posts: n/a
Default


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


  #4   Report Post  
Max
 
Posts: n/a
Default

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

  #5   Report Post  
R.VENKATARAMAN
 
Posts: n/a
Default

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





  #6   Report Post  
Max
 
Posts: n/a
Default

"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
---
  #7   Report Post  
Biff
 
Posts: n/a
Default

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


.

  #8   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

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



  #9   Report Post  
Max
 
Posts: n/a
Default

"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
----

  #10   Report Post  
KL
 
Posts: n/a
Default

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




  #11   Report Post  
Biff
 
Posts: n/a
Default

Hi!

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

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

Biff

-----Original Message-----
....and doesn't seem to tolerate empty cells.

KL

"Max" wrote in message=20
...
"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"
before it worked ..

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


.

  #12   Report Post  
Biff
 
Posts: n/a
Default

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

.

  #13   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

All formulas in this branch are working, when:
There is a number in range,
or there is a number preceeded with * in range,
or there is "Trace" in range (is ignored).

But as I understood, "Trace" is only one possible text entry, given as
example - but there can be others too. And I have a feeling, such text
entries aren't limited in any way. When this is the case, the array formula
I did give, will work, when:
There is a number in range,
or there is a number preceeded with * in range,
or there is any text entry in range (is ignored).

Arvi Laanemets


"Biff" wrote in message
...
Hi!

=SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE
("0"&A1:A10,"Trace",""),"*","")))

Still shorter and still not an array with less function
calls! <g

Biff

-----Original Message-----
....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
----


.



  #14   Report Post  
Max
 
Posts: n/a
Default

Thanks, Biff !
Yes, it works ok now

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


Agreed <g
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Biff" wrote in message
...
Hi!

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


If case and blank cells need to be considered:

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

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

Biff


  #15   Report Post  
Max
 
Posts: n/a
Default

Stumbled across Aladin's 2 replies in Excelforum
(think his replies there somehow didn't propagate over ..)

Interesting deliberation. I presume you didn't see:
http://www.excelforum.com/printthread.php?t=320974


An his earlier post ..
If not inconvenient, go with R.VENKATARAMAN's suggestion.
Otherwise:

=SUM(IF(ISNUMBER(SUBSTITUTE(D5:D35,"*","")+0),SUBS TITUTE(D5:D35,"*","")+0,0)
)

which must be confirmed with control+shift+enter
instead of just with enter


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




  #16   Report Post  
Max
 
Posts: n/a
Default

Aladin wrote:
....
Interesting deliberation. I presume you didn't see:
http://www.excelforum.com/printthread.php?t=3D320974


Believe none of us would have seen the earlier response=20
simply because it didn't propagate through to the ng from=20
Excelforum

Agreed with Arvi's point that both his (and Aladin's)
suggested array formulas are perhaps the most robust=20
if one extends the interp. of the OP

Marvellous, exhilarating discussion ..

--
Rgds
Max
xl 97
---
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
----
  #17   Report Post  
Biff
 
Posts: n/a
Default

Marvellous, exhilarating discussion ..

Agreed!

I love these threads where there are many interpretations=20
of a solution. Better than any book, that's for sure!

The only question that I pose is: when does robust turn=20
into overkill? Usually, that's something only the OP can=20
help eliminate by being very explicit when they post and=20
as we know, that doesn't always happen.

Biff=20

-----Original Message-----
Aladin wrote:
....
Interesting deliberation. I presume you didn't see:
http://www.excelforum.com/printthread.php?t=3D320974


Believe none of us would have seen the earlier response=20
simply because it didn't propagate through to the ng from=20
Excelforum

Agreed with Arvi's point that both his (and Aladin's)
suggested array formulas are perhaps the most robust=20
if one extends the interp. of the OP

Marvellous, exhilarating discussion ..

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

  #18   Report Post  
Kay
 
Posts: n/a
Default

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)
)


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use SUMIF to return sums between two values located in cells ScottBerger Excel Worksheet Functions 2 April 23rd 23 09:05 PM
Counting Rainfall Data TightIsobars Excel Discussion (Misc queries) 2 January 18th 05 12:45 AM
Excel Rainfall Chart template MichaelAN Charts and Charting in Excel 2 January 5th 05 03:56 AM
SUMIF across a range of worksheets Mike@Q Excel Worksheet Functions 3 November 24th 04 03:36 PM
SUM(IF( Array to avoid #NUM! values Elijah Excel Worksheet Functions 7 November 21st 04 03:17 PM


All times are GMT +1. The time now is 02:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"