# Minimum 10, Maximum 10 from a series

#### SarahKC

##### New Member
Hi there,

I have data whereby I would like the sum of the top 10 numbers in a row and the sum of the bottom 10 numbers in a row.

I have done this before by adding another row with ranking and then doing an If statement referencing the ranking.

I want to do this operation, however, over many rows so ranking would be a pain and sorting by each row and then doing it would also be a pain.

Thoughts?

Thanks,
Sarah

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### schielrn

##### Well-known Member
Welcome to the Board!

What if there is a tie for the 10th spot, how do you want that handled?

Example being ththis data:

1
2
3
4
5
6
7
8
9
10
10
11
12
13

Do you want to sum the 10 twice?

#### SarahKC

##### New Member
Thanks for the welcome.

Great question: no. I want one ten.

#### schielrn

##### Well-known Member
Ok that makes the formula a little longet, but this should work for the bottom ten:

=SUM(IF(A1:A15<=SMALL(A1:A15,10),A1:A15,0))-(COUNTIF(A1:A15,SMALL(A1:A15,10))-1)*small(A1:A15,10)

For the top 10:

=SUM(IF(A1:A15>=Large(A1:A15,10),A1:A15,0))-(COUNTIF(A1:A15,large(A1:A15,10))-1)*large(A1:A15,10)

Both of these are array formulas and need to be confirmed with control+shift+enter and not just enter. Hope that works.

#### SarahKC

##### New Member
Okay and now I have to figure out why this works so I can understand what I'm doing...

Replies
5
Views
134
Replies
8
Views
326
Replies
3
Views
232
Replies
1
Views
300
Replies
5
Views
551

1,190,957
Messages
5,983,837
Members
439,866
Latest member
jh3268

### 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