Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old November 29th 04, 07:23 PM
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
(D535,"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  
Old November 29th 04, 07:45 PM
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
(D535,"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  
Old November 29th 04, 07:53 PM
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
(D535,"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  
Old November 29th 04, 10:55 PM
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  
Old November 29th 04, 11:05 PM
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(D535,"*","")+0),SUBS TITUTE(D535,"*","")+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  
Old November 30th 04, 01:43 AM
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  
Old November 30th 04, 05:08 AM
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  
Old December 1st 04, 12:27 PM
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(D535,"*","")*1),SUB STITUTE(D535,"*","")*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
Counting Rainfall Data TightIsobars Excel Discussion (Misc queries) 2 January 17th 05 11:45 PM
Excel Rainfall Chart template MichaelAN Charts and Charting in Excel 2 January 5th 05 02:56 AM
SUMIF across a range of worksheets [email protected] Excel Worksheet Functions 3 November 24th 04 02:36 PM
SUM(IF( Array to avoid #NUM! values Elijah Excel Worksheet Functions 7 November 21st 04 02:17 PM
How to use SUMIF to return sums between two values located in cells ScottBerger Excel Worksheet Functions 1 November 18th 04 07:09 PM


All times are GMT +1. The time now is 06:11 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017