# Summing Top ten, top 20 etc

#### JV0710

##### Active Member
Hi

Please can you help me with the following query:

On sheet 1 I have a list of locations in column A. In Column B is the sales for the month and in column C, the Sales for the same month last year.

On Sheet 2, row 1, I would like to sum up the sales for the Top ten locations ( based on Column B in sheet 1) and also sum the corresponding last year sales ( column C in sheet 2).

On Row 2, I would like to do the same sum, but for locations 11 to 25.

On Row 3, I would like to do the same sum, but for the bottom 10 locations.

Note: The number of locations is not constant every day.

I would appreciate any help

Thank you

JVN

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### Lewiy

##### Well-known Member
Assuming that your range is A1:B30 on sheet1, then the top 10 would be:
Code:
``=SUMPRODUCT(--(Sheet1!B1:B30>=LARGE(Sheet1!B1:B30,10)), Sheet1!B1:B30)``
Bottom 10:
Code:
``=SUMPRODUCT(--(Sheet1!B1:B30<=SMALL(Sheet1!B1:B30,10)), Sheet1!B1:B30)``
11 to 25:
Code:
``=SUMPRODUCT(--(Sheet1!B1:B30>=LARGE(Sheet1!B1:B30,25)),--(B1:B30<=LARGE(Sheet1!B1:B30,11)), Sheet1!B1:B30)``

#### lenze

##### Legend
A Pivot Table would do this too!!

lenze

#### Dan Waterloo

##### Well-known Member
Lewiy: I am curious as to why you included the double negatives in your formulas. Would you please explain?

#### JV0710

##### Active Member
Thanks Lewiy

One question though : Assuming my range is A1:B30 and next week there is only info up to row 25, How do I get it ignore the bottom 5 rows that now are equal to zero ?

thanks

JVN

EDIT: deleted 5 duplicate posts...Just hit the submit button once OK? Sometimes the board's slow. :wink: Smitty

#### Lewiy

##### Well-known Member
Try these:

Top 10:
Code:
``=SUMPRODUCT(--(B1:B30>=LARGE(B1:B30,10)),--(B1:B30<>0),B1:B30)``
Bottom 10:
Code:
``=SUMPRODUCT(--(B1:B30<=SMALL(B1:B30,10)),--(B1:B30<>0),B1:B30)``
11 to 25
Code:
``=SUMPRODUCT(--(B1:B30>=LARGE(B1:B30,20)),--(B1:B30<=LARGE(B1:B30,11)),--(B1:B30<>0),B1:B30)``

#### JV0710

##### Active Member

=SUMPRODUCT(--(B1:B30<=SMALL(B1:B30,10)),--(B1:B30<>0),B1:B30)

To sum up the bottom 10 . . . But it is still including the cells with Zero as part of the bottom 10.

#### Lewiy

##### Well-known Member
Hmm, I’ve noticed that there’s another problem with this. If your smallest number was 2 and you had 13 2’s in the column, it will add them all together giving you 26 when really you want 20. May have to re-think this a bit.

Lewiy: I am curious as to why you included the double negatives in your formulas. Would you please explain?
Sorry must have missed this question before!
The double negatives coerce the results into numbers. The generic evaluation A<=B will return either TRUE or FALSE so the SUMPRODUCT formula ends up with an array of TRUEs and FALSEs. By including a mathematical operator, this turns into an array of 1’s and 0’s respectively.

#### JV0710

##### Active Member
Hi Lewiy

The list i am working with is the sales for about 900 Stores. It will be very rare for Stores to have exactly the same sales figures ( But if they do, then adding them, as per your example is fine). The problem I am having is that if the bottom 10 stores are in the bottom 12 because 2 stores have zero, then I want to sum the bottom 10 Excluding the zero's.

Thanks
JVN

#### barry houdini

##### MrExcel MVP
To sum the smallest 10 non-zero values try

=SUM(SMALL(IF(B1:B30,B1:B30),ROW(INDIRECT("1:"&MIN(10,COUNTIF(B1:B30,">0"))))))

confirmed with CTRL+SHIFT+ENTER

note: if there are less than 10 non-zero values this will sum them all

Replies
5
Views
178
Replies
6
Views
176
Replies
7
Views
200
Replies
17
Views
447
Replies
5
Views
300

1,181,102
Messages
5,928,067
Members
436,586
Latest member
latintxn

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