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 |
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 |
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 |
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 |
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 |
"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 . |
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 |
"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!
=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 ---- . |
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 ---- . |
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 ---- . |
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 |
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 ---- |
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 ---- |
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 ---- . |
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