Sumif Rainfall

  • Thread starter Thread starter Kay
  • Start date Start date
K

Kay

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

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

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

Biff
 
Hi

Another one (an array function too)
=SUM(IF(ISNUMBER(SUBSTITUTE(D5:D35,"*","")*1),SUBSTITUTE(D5:D35,"*","")*1,0)
)
 
Biff said:
Here's something a little shorter
and it's not an array:
=SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE
(D5:D35,"trace","0"),"*","")))
Click to expand...

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

Had to change "trace" to "Trace"
before it worked ..
 
....and doesn't seem to tolerate empty cells.

KL

Biff said:
Here's something a little shorter
and it's not an array:
=SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE
(D5:D35,"trace","0"),"*","")))
Click to expand...

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

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

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

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

Biff
 
Hi!
Think it's case sensitive, Biff
(unlike the longer, array formula <g>)
Click to expand...

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


Hi!

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

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

Biff
 
Thanks, Biff !
Yes, it works ok now
Still shorter, still not an array,
and still has less function calls! <g>
Click to expand...

Agreed <g>
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
Hi!
Think it's case sensitive, Biff
(unlike the longer, array formula <g>)
Click to expand...

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
 
Aladin wrote:
....
Believe none of us would have seen the earlier response
simply because it didn't propagate through to the ng from
Excelforum

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

Marvellous, exhilarating discussion ..
 
Marvellous, exhilarating discussion ..

Agreed!

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

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

Biff
 
Back
Top