# If greater or less than unique vaules

#### Fin Fang Foom

##### Well-known Member
Hi everyone,

I have this formula below it will retrieve unique vaules and sort them in a Ascending order.

=INDEX(\$A\$2:\$A\$1000,MATCH(SUM(COUNTIF(\$A\$2:\$A\$1000,C\$1:C1)),COUNTIF(\$A\$2:\$A\$1000,"<"&\$A\$2:\$A\$1000)+ISTEXT(\$A\$2:\$A\$1000)*SUM(--ISNUMBER(\$A\$2:\$A\$1000))-ISBLANK(\$A\$2:\$A\$1000),0))

But I would like to add a condition in the formula. If greater or equal to 3 and Less or equal to 9.

I tried to modifed it but no luck.

=INDEX(\$A\$2:\$A\$1000,MATCH(SUM(COUNTIF(\$A\$2:\$A\$1000,C\$1:C1)),IF(\$A\$2:\$A\$1000>=3,IF(A\$2:A\$1000<=9,COUNTIF(\$A\$2:\$A\$1000,"<"&\$A\$2:\$A\$1000)+ISTEXT(\$A\$2:\$A\$1000)*SUM(--ISNUMBER(\$A\$2:\$A\$1000))-ISBLANK(\$A\$2:\$A\$1000),0))))

The expected results I'm looking for are in column E
Book3
ABCDEF
1REASON#Expected Results
2113
3225
4336
51047
6558
7669
877
988
1099
111010
121111
131212
14314
151415
161518
17819
18720
191821
201922
Sheet1

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### Jon von der Heyden

##### MrExcel MVP, Moderator
I've done it in two stages:
Book3
ABCD
1No #Unique
2773
311114
417175
5706
6337
71108
81818#NUM!
944#NUM!
101212
112020
1266
1388
14110
151616
1670
1755
18180
191515
20160
211414
22110
Sheet1

In column B I've created a list of unique items, all duplicated values now appear as 0 (ommitted from SMALL() result)

Column D uses a SMALL(IF()) array formula: =SMALL(IF((\$B\$2:\$B\$22>=3)*(\$B\$2:\$B\$22<=9),\$B\$2:\$B\$22),ROW()-1)

Hope this helps

#### Fin Fang Foom

##### Well-known Member
Thanks Jon von der Heyden. I should've mention I'm looking for a all in one formula. Because I will incorporate the formula into a macro.

Again I tried to modified it and still it does not work.

=INDEX(\$A\$2:\$A\$1000,MATCH(SUM(COUNTIF(\$A\$2:\$A\$1000,F\$1:F1)),IF((\$A\$2:\$A\$1000>=3)*(\$A\$2:\$A\$1000<=29),COUNTIF(\$A\$2:\$A\$1000,"<"&\$A\$2:\$A\$1000)+ISTEXT(\$A\$2:\$A\$1000)*SUM(--ISNUMBER(\$A\$2:\$A\$1000)))-ISBLANK(\$A\$2:\$A\$1000),0))

I dont know what I'm missing.

Bump

#### Domenic

##### MrExcel MVP
Since the data contains numerical values, maybe...

B2:

=COUNT(1/FREQUENCY(IF(A2:A19>=3,IF(A2:A19<=9,A2:A19)),IF(A2:A19>=3,IF(A2:A19<=9,A2:A19))))

...confirmed with CONTROL+SHIFT+ENTER

C2, copied down:

=IF(ROWS(\$C\$2:C2)<=\$B\$2,MIN(IF(\$A\$2:\$A\$19>=3,IF(\$A\$2:\$A\$19<=9,IF(ISNA(MATCH(\$A\$2:\$A\$19,\$C\$1:C1,0)),\$A\$2:\$A\$19)))),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

#### Fin Fang Foom

##### Well-known Member
Once again thank You for your magic!

Thank You!

Replies
0
Views
377
Replies
6
Views
343
Replies
7
Views
247
Replies
4
Views
241
Replies
9
Views
199

Threads
1,171,588
Messages
5,876,320
Members
433,193
Latest member
BruxoTrader

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back