ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Sumif Rainfall (https://www.excelbanter.com/excel-worksheet-functions/7175-sumif-rainfall.html)

Kay

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

Kay

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


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



Max

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


R.VENKATARAMAN

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




Max

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

Biff

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


.


Arvi Laanemets

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




Max

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


KL

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



Biff

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


.


Biff

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

.


Arvi Laanemets

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


.




Max

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



Max

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



Max

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

Biff

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


Kay

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




All times are GMT +1. The time now is 07:01 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com