Thread: Sumif Rainfall
View Single Post
  #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
----


.