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

 

[Peter_SSs]

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

See the links in the second post of this thread
http://www.mrexcel.com/board2/viewtopic.php?t=270047

 

[Peter_SSs]

Would this work for the bottom 10?

Excel Workbook
	A	B	C	D
1		0		20
2		0
3		0
4		0
5		0
6		2
7		2
8		2
9		2
10		2
11		2
12		2
13		2
14		2
15		2
16		2
17		2
18		2
19		3
20		4
Sum Bottom 10

 

[JV0710]

Thank you Barry

This formula works when Summing the smallest 10 in column B. . . . How do I adjust it so that I can sum the corresponding values in column C ?

Example: the smallest 10 in column B sums up to 40 and the same Cells in column C sum up to 35.

Thanks

JVN

 

[JV0710]

Thanks Peter

This will work . . Although As I mentioned, I will want to sum up the corresponding values in other columns as well.... Plus, I am trying to make the sheet flexible, so I have a cell reference in another cell that allows me to input 10, 20, 30 etc depending on wether I want to see the sum of the top 10, 20 , 30 etc.

 

[JV0710]

I have figurd out that If I filter column A to show only values above 0, then Lewiy's original formula works fine. The problem I am still faced with, is that I want to be able to see and sum other columns where column A has zero values

 

[JV0710]

I have figurd out that If I filter column A to show only values above 0, then Lewiy's original formula works fine. The problem I am still faced with, is that I want to be able to see and sum other columns where column A has zero values

 

[Domenic]

To sum Column C where the corresponding values in Column B are the 10 smallest non-zero numbers, try...

=SUM(IF(B1:B20>0,IF(RANK(B1:B20,B1:B20,1)+COUNTIF(OFFSET(B1:B20,,,ROW(B1:B20)-ROW(B1)+1),B1:B20)-1<=COUNTIF(B1:B20,0)+10,C1:C20)))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the ranges accordingly. Note that if there's more than one number in Column B tied for the 10th smallest number, only the corresponding value for the first instance of 10th will be summed. For example, if Column B and C contained the following...

67	3
6	92
73	50
0	32
22	78
0	22
25	31
2	7
79	18
0	100
74	36
12	68
23	65
0	6
65	73
0	35
67	66
14	62
0	17
45	29

...the formula will return 508 (7+92+68+62+78+65+31+29+73+3)

Hope this helps!

 

[JV0710]

Thanks Domenic

Your array formula seems to do the trick . . . I will test it on the completed spreadsheet and see if the scenario where " more than one number in Column B tied for the 10th smallest number . . . " comes up or creates a problem.

Thanks very much for this . . .