Combining formulas does not work

[vsoler]

I have the following model:

............A........B
1.........1........blank
2.........2........a
3.........3........a
4.........4........1
5.........5........2
6.........6........blank
7.........7........b
8.........8........blank
9.........9........1

In C1 I have:
=IF(COUNTIF($B$1:B1;B1)=1;A1;10)
which I copy down to C9

In D1 I have:
=SMALL($C$1:$C$9;A1)
which I copy down to D9

In this D column I get:
2
4
5
7
10
10
10
10
10
which is what I want.

However, combining the two formulas into one does not work. I've tried
in E1:
=SMALL(IF(COUNTIF($B$1:B1;B1)=1;A1;10):A1) which I copy down to E9

I get:
10
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!
#NUM!

Nothing to do with the desired result. I have no idea why it doesn't
work.

Any help?

 

[T. Valko]

Try this array formula**

A1:A9 = rng1
B1:B9 = rng2

=IF(ROWS($1:1)<=SUM((rng2<>"")/COUNTIF(rng2,rng2&"")),INDEX(rng1,SMALL(IF(rng2<>"",IF(ROW(rng2)-MIN(ROW(rng2))+1=MATCH(rng2,rng2,0),ROW(rng2)-MIN(ROW(rng2))+1)),ROWS($1:1))),10)

Copy down as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

 

[vsoler]

Try this array formula**

A1:A9 = rng1
B1:B9 = rng2

=IF(ROWS($1:1)<=SUM((rng2<>"")/COUNTIF(rng2,rng2&"")),INDEX(rng1,SMALL(IF(r­ng2<>"",IF(ROW(rng2)-MIN(ROW(rng2))+1=MATCH(rng2,rng2,0),ROW(rng2)-MIN(ROW(­rng2))+1)),ROWS($1:1))),10)

Copy down as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff












- Mostrar texto de la cita -

T. Valko,

Your great. I wish I could write formulas like you do.

You always help a lot.

Thank you

 

[T. Valko]

You're welcome. Thanks for the feedback!

I wish I could write formulas like you do.

There's no reason why you can't. Spend a couple of hours a day in these
forums and study the replies and practice them using different scenarios.
Before you know it, you'll be writing formulas with the best of 'em (except
Harlan).

Biff

Try this array formula**

A1:A9 = rng1
B1:B9 = rng2

=IF(ROWS($1:1)<=SUM((rng2<>"")/COUNTIF(rng2,rng2&"")),INDEX(rng1,SMALL(IF(r­ng2<>"",IF(ROW(rng2)-MIN(ROW(rng2))+1=MATCH(rng2,rng2,0),ROW(rng2)-MIN(ROW(­rng2))+1)),ROWS($1:1))),10)

Copy down as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff












- Mostrar texto de la cita -

T. Valko,

Your great. I wish I could write formulas like you do.

You always help a lot.

Thank you