Each cells R54:R82 has a range of number in it. Column S, T, and U are
updated everyday. This is an example of rows 54, 55, and 56:

R54,S54,T54,U54

0-.4, 1, 0, 1.5
..5-.9, 0, 0, 0
1.0-1.5, 2, 1, 1.3

I would like to setup a formula in another column that will pick our the
rows that have a number greater than 0 in either column S, T, or U.

So in the above example I would get this:
0-.4, 1, 0, 1.5
1.0-1.5, 2, 1, 1.3

Thank you

Try the following...

W54:

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(S54:U82,ROW(S54:U82)-ROW(S54),0,1))0))

X54, copied across and down:

=IF(ROWS(X$54:X54)<=$W$54,INDEX(R$54:R$82,SMALL(IF (SUBTOTAL(9,OFFSET($S$5
4:$U$82,ROW($S$54:$U$82)-ROW($S$54),0,1)),ROW($R$54:$R$82)-ROW($R$54)+1),
ROWS(X$54:X54))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
Jambruins wrote:

Each cells R54:R82 has a range of number in it. Column S, T, and U are
updated everyday. This is an example of rows 54, 55, and 56:

R54,S54,T54,U54

0-.4, 1, 0, 1.5
.5-.9, 0, 0, 0
1.0-1.5, 2, 1, 1.3

I would like to setup a formula in another column that will pick our the
rows that have a number greater than 0 in either column S, T, or U.

So in the above example I would get this:
0-.4, 1, 0, 1.5
1.0-1.5, 2, 1, 1.3

Thank you

nevermind, I found my error, thanks

"Domenic" wrote:

Try the following...

W54:

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(S54:U82,ROW(S54:U82)-ROW(S54),0,1))0))

X54, copied across and down:

=IF(ROWS(X$54:X54)<=$W$54,INDEX(R$54:R$82,SMALL(IF (SUBTOTAL(9,OFFSET($S$5
4:$U$82,ROW($S$54:$U$82)-ROW($S$54),0,1)),ROW($R$54:$R$82)-ROW($R$54)+1),
ROWS(X$54:X54))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
Jambruins wrote:

Each cells R54:R82 has a range of number in it. Column S, T, and U are
updated everyday. This is an example of rows 54, 55, and 56:

R54,S54,T54,U54

0-.4, 1, 0, 1.5
.5-.9, 0, 0, 0
1.0-1.5, 2, 1, 1.3

I would like to setup a formula in another column that will pick our the
rows that have a number greater than 0 in either column S, T, or U.

So in the above example I would get this:
0-.4, 1, 0, 1.5
1.0-1.5, 2, 1, 1.3

Thank you

Domenic,
That works for cell x54,y54,z54, and AA54 but when I copy it down I get
#NUM! Any idea why? Thanks for the help.

"Domenic" wrote:

Try the following...

W54:

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(S54:U82,ROW(S54:U82)-ROW(S54),0,1))0))

X54, copied across and down:

=IF(ROWS(X$54:X54)<=$W$54,INDEX(R$54:R$82,SMALL(IF (SUBTOTAL(9,OFFSET($S$5
4:$U$82,ROW($S$54:$U$82)-ROW($S$54),0,1)),ROW($R$54:$R$82)-ROW($R$54)+1),
ROWS(X$54:X54))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
Jambruins wrote:

Each cells R54:R82 has a range of number in it. Column S, T, and U are
updated everyday. This is an example of rows 54, 55, and 56:

R54,S54,T54,U54

0-.4, 1, 0, 1.5
.5-.9, 0, 0, 0
1.0-1.5, 2, 1, 1.3

I would like to setup a formula in another column that will pick our the
rows that have a number greater than 0 in either column S, T, or U.

So in the above example I would get this:
0-.4, 1, 0, 1.5
1.0-1.5, 2, 1, 1.3

Thank you

Domenic,
Actually there is a little problem. W54 = 8 when that formula is entered.
In some rows column S is 0 but column T is not. I need W54 to take into
account column T also. Any ideas? Thanks

"Jambruins" wrote:

Each cells R54:R82 has a range of number in it. Column S, T, and U are
updated everyday. This is an example of rows 54, 55, and 56:

R54,S54,T54,U54

0-.4, 1, 0, 1.5
.5-.9, 0, 0, 0
1.0-1.5, 2, 1, 1.3

I would like to setup a formula in another column that will pick our the
rows that have a number greater than 0 in either column S, T, or U.

So in the above example I would get this:
0-.4, 1, 0, 1.5
1.0-1.5, 2, 1, 1.3

Thank you

Domenic,
I figured out my problem. It probably isn't the best way to make it work
but it does the job. Thanks for you help.

"Jambruins" wrote:

Domenic,
Actually there is a little problem. W54 = 8 when that formula is entered.
In some rows column S is 0 but column T is not. I need W54 to take into
account column T also. Any ideas? Thanks

"Jambruins" wrote:

Each cells R54:R82 has a range of number in it. Column S, T, and U are
updated everyday. This is an example of rows 54, 55, and 56:

R54,S54,T54,U54

0-.4, 1, 0, 1.5
.5-.9, 0, 0, 0
1.0-1.5, 2, 1, 1.3

I would like to setup a formula in another column that will pick our the
rows that have a number greater than 0 in either column S, T, or U.

So in the above example I would get this:
0-.4, 1, 0, 1.5
1.0-1.5, 2, 1, 1.3

Thank you

In article ,
Jambruins wrote:

Domenic,
I figured out my problem. It probably isn't the best way to make it work
but it does the job.

If you'd like, you can tell me what the problem is and I'll see if I can
change/modify the formula.

Here is the problem: W54 = 8 when that formula is entered.
In some rows column S is 0 but column T is not. I need W54 to take into
account column T also. Any ideas? Thanks


"Domenic" wrote:

In article ,
Jambruins wrote:

Domenic,
I figured out my problem. It probably isn't the best way to make it work
but it does the job.

If you'd like, you can tell me what the problem is and I'll see if I can
change/modify the formula.

In article ,
Jambruins wrote:

Here is the problem: W54 = 8 when that formula is entered.
In some rows column S is 0 but column T is not. I need W54 to take into
account column T also. Any ideas? Thanks

Actually, the formula I offered will take into account Column S, T, and
U, as you requested. Can you post the exact formula that you're using?

Here are columns R, S, T, and U (rows 54 to 82).
0-.4 2 1 0.95
..5-.9 1 2 -1.26
1.0-1.4 0 0 0.00
+1.5 0 1 -1.40
-1 RL 0 1 -1.00
RL 0 0 0.00
ARL 0 0 0.00
+-200 0 0 0.00
-190/-199 0 0 0.00
-180/-189 0 0 0.00
-170/-179 0 1 -1.76
-160/-169 0 0 0.00
-150/-159 0 0 0.00
-140/-149 0 1 -1.40
-130/-139 0 0 0.00
-120/-129 0 0 0.00
-110/-119 0 1 -1.12
-101/-109 1 0 1.00
100/109 1 0 1.07
110/119 0 1 -1.00
120/129 0 0 0.00
130/139 0 0 0.00
140/149 0 0 0.00
150/159 1 0 1.50
160/169 0 0 0.00
170/179 0 0 0.00
180/189 0 0 0.00
190/199 0 0 0.00
+200 0 0 0.00

I put
=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(S54:U82,ROW(S54:U82)-ROW(S54),0,1))0)) into
W54. It gives me 5 (I assume it is getting 5 from column S). It should be
11.

I put
=IF(ROWS(X$54:X54)<=$W$54,INDEX(R$54:R$82,SMALL(IF (SUBTOTAL(9,OFFSET($S$54:$U$82,ROW($S$54:$U$82)-ROW($S$54),0,1)),ROW($R$54:$R$82)-ROW($R$54)+1),ROWS(X$54:X54))),"") into X54 and copied it across and then down. This is what I get:

0-.4 2 1 0.95
..5-.9 1 2 -1.26
+1.5 0 1 -1.4
-170/-179 0 1 -1.76
-140/-149 0 1 -1.4

you can see it is skipping some rows it shouldn't be (like -1 RL). I am
using ctrl,shift,enter when applying the fomula in X54, Y54, Z54, and AA54.

Any idea what is wrong? Thanks for the help.

Since your data includes negative numbers, try the following instead...

W54:

=SUMPRODUCT(--(MMULT(--(S54:U82<0),{1;1;1})0))

....confirmed with just ENTER

X54, copied across and down:

=IF(ROWS(X$54:X54)<=$W$54,INDEX(R$54:R$82,SMALL(IF (MMULT(--($S$54:$U$82<
0),{1;1;1})0,ROW($R$54:$R$82)-ROW($R$54)+1),ROWS(X$54:X54))),"")

....confirmed with CONTROL+SHIFT+ENTER

Hope this helps!

In article ,
Jambruins wrote:

Here are columns R, S, T, and U (rows 54 to 82).
0-.4 2 1 0.95
.5-.9 1 2 -1.26
1.0-1.4 0 0 0.00
+1.5 0 1 -1.40
-1 RL 0 1 -1.00
RL 0 0 0.00
ARL 0 0 0.00
+-200 0 0 0.00
-190/-199 0 0 0.00
-180/-189 0 0 0.00
-170/-179 0 1 -1.76
-160/-169 0 0 0.00
-150/-159 0 0 0.00
-140/-149 0 1 -1.40
-130/-139 0 0 0.00
-120/-129 0 0 0.00
-110/-119 0 1 -1.12
-101/-109 1 0 1.00
100/109 1 0 1.07
110/119 0 1 -1.00
120/129 0 0 0.00
130/139 0 0 0.00
140/149 0 0 0.00
150/159 1 0 1.50
160/169 0 0 0.00
170/179 0 0 0.00
180/189 0 0 0.00
190/199 0 0 0.00
+200 0 0 0.00

I put
=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(S54:U82,ROW(S54:U82)-ROW(S54),0,1))0)) into
W54. It gives me 5 (I assume it is getting 5 from column S). It should be
11.

I put
=IF(ROWS(X$54:X54)<=$W$54,INDEX(R$54:R$82,SMALL(IF (SUBTOTAL(9,OFFSET($S$54:$U$
82,ROW($S$54:$U$82)-ROW($S$54),0,1)),ROW($R$54:$R$82)-ROW($R$54)+1),ROWS(X$54:
X54))),"") into X54 and copied it across and then down. This is what I get:

0-.4 2 1 0.95
.5-.9 1 2 -1.26
+1.5 0 1 -1.4
-170/-179 0 1 -1.76
-140/-149 0 1 -1.4

you can see it is skipping some rows it shouldn't be (like -1 RL). I am
using ctrl,shift,enter when applying the fomula in X54, Y54, Z54, and AA54.

Any idea what is wrong? Thanks for the help.

perfect, thank you very much.

"Domenic" wrote:

Since your data includes negative numbers, try the following instead...

W54:

=SUMPRODUCT(--(MMULT(--(S54:U82<0),{1;1;1})0))

....confirmed with just ENTER

X54, copied across and down:

=IF(ROWS(X$54:X54)<=$W$54,INDEX(R$54:R$82,SMALL(IF (MMULT(--($S$54:$U$82<
0),{1;1;1})0,ROW($R$54:$R$82)-ROW($R$54)+1),ROWS(X$54:X54))),"")

....confirmed with CONTROL+SHIFT+ENTER

Hope this helps!

In article ,
Jambruins wrote:

Here are columns R, S, T, and U (rows 54 to 82).
0-.4 2 1 0.95
.5-.9 1 2 -1.26
1.0-1.4 0 0 0.00
+1.5 0 1 -1.40
-1 RL 0 1 -1.00
RL 0 0 0.00
ARL 0 0 0.00
+-200 0 0 0.00
-190/-199 0 0 0.00
-180/-189 0 0 0.00
-170/-179 0 1 -1.76
-160/-169 0 0 0.00
-150/-159 0 0 0.00
-140/-149 0 1 -1.40
-130/-139 0 0 0.00
-120/-129 0 0 0.00
-110/-119 0 1 -1.12
-101/-109 1 0 1.00
100/109 1 0 1.07
110/119 0 1 -1.00
120/129 0 0 0.00
130/139 0 0 0.00
140/149 0 0 0.00
150/159 1 0 1.50
160/169 0 0 0.00
170/179 0 0 0.00
180/189 0 0 0.00
190/199 0 0 0.00
+200 0 0 0.00

I put
=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(S54:U82,ROW(S54:U82)-ROW(S54),0,1))0)) into
W54. It gives me 5 (I assume it is getting 5 from column S). It should be
11.

I put
=IF(ROWS(X$54:X54)<=$W$54,INDEX(R$54:R$82,SMALL(IF (SUBTOTAL(9,OFFSET($S$54:$U$
82,ROW($S$54:$U$82)-ROW($S$54),0,1)),ROW($R$54:$R$82)-ROW($R$54)+1),ROWS(X$54:
X54))),"") into X54 and copied it across and then down. This is what I get:

0-.4 2 1 0.95
.5-.9 1 2 -1.26
+1.5 0 1 -1.4
-170/-179 0 1 -1.76
-140/-149 0 1 -1.4

you can see it is skipping some rows it shouldn't be (like -1 RL). I am
using ctrl,shift,enter when applying the fomula in X54, Y54, Z54, and AA54.

Any idea what is wrong? Thanks for the help.