If greater or less than unique vaules

Fin Fang Foom

Well-known Member
Joined
Mar 20, 2005
Messages
598
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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 20, 2005
Messages
598
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.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,696
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!
 

Forum statistics

Threads
1,136,268
Messages
5,674,734
Members
419,523
Latest member
Urnovio

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

Which adblocker are you using?

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
Top