Summing Top ten, top 20 etc

JV0710

Active Member
Joined
Oct 26, 2006
Messages
396
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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
Joined
Jan 5, 2007
Messages
4,284
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)
 

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
876
Lewiy: I am curious as to why you included the double negatives in your formulas. Would you please explain?
 

JV0710

Active Member
Joined
Oct 26, 2006
Messages
396
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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
Joined
Jan 5, 2007
Messages
4,284
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
Joined
Oct 26, 2006
Messages
396
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Thanks for your Reply Lewiy . . .

I am using your code:

=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
Joined
Jan 5, 2007
Messages
4,284
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! :oops:
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
Joined
Oct 26, 2006
Messages
396
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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
Joined
Mar 23, 2005
Messages
20,825
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
 

Forum statistics

Threads
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.
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