# Deleting 1-5 lowest UNIQUE numbers in a range of cells

#### Centrewelt

##### New Member
=IF(J223=0,SUM(J132:J186),IF(J223=1,SUM(J132:J186)-SMALL(J132:J186,COUNTIF(J132:J186,0)+1),IF(J223=2,SUM(J132:J186)-SMALL(J132:J186,COUNTIF(J132:J186,0)+1)-SMALL(J132:J186,COUNTIF(J132:J186,0)+1),IF(J223=3,SUM(J132:J186)-SMALL(J132:J186,COUNTIF(J132:J186,0)+1)-SMALL(J132:J186,COUNTIF(J132:J186,0)+1)-SMALL(J132:J186,COUNTIF(J132:J186,0)+1),IF(J223=4,SUM(J132:J186)-SMALL(J132:J186,COUNTIF(J132:J186,0)+1)-SMALL(J132:J186,COUNTIF(J132:J186,0)+1)-SMALL(J132:J186,COUNTIF(J132:J186,0)+1)-SMALL(J132:J186,COUNTIF(J132:J186,0)+1),IF(J223=5,SUM(J132:J186)-SMALL(J132:J186,COUNTIF(J132:J186,0)+1)-SMALL(J132:J186,COUNTIF(J132:J186,0)+1)-SMALL(J132:J186,COUNTIF(J132:J186,0)+1)-SMALL(J132:J186,COUNTIF(J132:J186,0)+1)-SMALL(J132:J186,COUNTIF(J132:J186,0)+1)))))))

I am a new user and apologize for the length of the formula.

I am trying to delete the lowest UNIQUE values between J132:J186. The number of UNIQUE numbers to delete depends on whether J223 is 1, 2, 3, 4, or 5. For example, if J223 is 3, then delete 3 lowest UNIQUE numbers. The above formula works when the lowest numbers are the same, but not when they are different. You are supposed to be able to tell SMALL the number of UNIQUE numbers to delete via "k" at the end of SMALL, but I am not sure how to do this with COUNTIF at the end.

Thanks.

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Given

5
7
4
3
3
9
2

delete 3 lowest UNIQUE values. What would be the result list?

The result should be 25.

1) 5+7+4+3+3+9+2 = 33
2) 33-3-3-2=25

Not (like my formula)

1) 5+7+4+3+3+9+2 = 33
2) 2 (lowest value) x 3 (number of times to delete)= 6
3) 33-6=27

[/u]

Replies
15
Views
269
Replies
6
Views
255
Replies
6
Views
264
Replies
22
Views
434
Replies
3
Views
286

### Forum statistics

1,203,061
Messages
6,053,307
Members
444,651
Latest member
markkuznetsov1 ### 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.

### Which adblocker are you using?    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

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