Summing Top ten, top 20 etc

[JV0710]

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

 

[Lewiy]

Assuming that your range is A1:B30 on sheet1, then the top 10 would be:

=SUMPRODUCT(--(Sheet1!B1:B30>=LARGE(Sheet1!B1:B30,10)), Sheet1!B1:B30)

Bottom 10:

=SUMPRODUCT(--(Sheet1!B1:B30<=SMALL(Sheet1!B1:B30,10)), Sheet1!B1:B30)

11 to 25:

=SUMPRODUCT(--(Sheet1!B1:B30>=LARGE(Sheet1!B1:B30,25)),--(B1:B30<=LARGE(Sheet1!B1:B30,11)), Sheet1!B1:B30)

 

[lenze]

A Pivot Table would do this too!!

lenze

 

[Dan Waterloo]

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

 

[JV0710]

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]

Try these:

Top 10:

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

Bottom 10:

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

11 to 25

=SUMPRODUCT(--(B1:B30>=LARGE(B1:B30,20)),--(B1:B30<=LARGE(B1:B30,11)),--(B1:B30<>0),B1:B30)

 

[JV0710]

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]

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]

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]

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