combine SUMIF with SMALL

[Bilingual]

​Hi, i would like to combine SUMIF with SMALL but has some problems doing so:

I have a table where Week/Year is stored and now i need to create a Top 10 of the worst results for each week.

I can easily do it with the whole data set by using =SMALL(Tabel1[Percentage];Oversigt_!A3), but i can not figure out to apply the Weekfilter, van anyone help? - i have attached the file here in Dropbox:
https://www.dropbox.com/s/nl4ozodtkqygnh5/Top 10.xlsx?dl=0

 

[AhoyNC]

Not sure why you want to use SUMIF if all you want is the ten smallest per week.
See if this array formula does what you want.
It must be entered with CTRL-SHIFT-ENTER. Then drag down rows as needed.

Excel Workbook
	A	B	C
1		???
2	Top 10	Uge 40 - 2018	Uge 41 - 2018
3	1	47%	43%
4	2	52%	47%
5	3	58%	48%
6	4	59%	56%
7	5	63%	58%
8	6	63%	61%
9	7	66%	66%
10	8	68%	67%
11	9	68%	68%
12	10	69%	69%
Sheet

 

[Bilingual]

Thanks a lot AhoyNC, just what i needed!

 

[Bilingual]

Hi AhoyNC, you helped me a lot last time, one last request.

If i want to keep your formula but change the output from the SMALL from the Tabel1[Percentage], but instead return the value from the column before, will it be possible? - the formula still needs to use the SMALL to find the correct values in the Tabel1[Percentage] column.

 

[AhoyNC]

Like this?

Excel Workbook
	A	B
1
2	Top 10
3	1	Uge 41 - 2018
4	2	Uge 40 - 2018
5	3	Uge 40 - 2018
6	4	Uge 41 - 2018
7	5	Uge 40 - 2018
8	6	Uge 41 - 2018
9	7	Uge 40 - 2018
10	8	Uge 41 - 2018
11	9	Uge 40 - 2018
12	10	Uge 41 - 2018
Sheet

 

[Bilingual]

Thanks a lot for your tries, however i am afraid i did not make myself clear, if you should have the time and patience, i have a dropbox link:

https://www.dropbox.com/s/5mybbnc5xf37ve7/Top 10.xlsb?dl=0


The problem is that is that i can easily with your help find the top 10 worst with /MATCH/SMALL but when i want to return the items to the value found, when similar values in Percentage occur, the formula in Items returns the first value twice instead of the corresponding value.

 

[AhoyNC]

Maybe something like this:
Formulas in G3 and H2 are array formulas that must be entered with CTRL-SHIFT-ENTER.
Drag formula in G3 down as needed. Drag formula in H2 down and across as needed.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Period	Item	Percentage	Combined		Rank	Percentage	Item(s)
2	Uge 40 - 2018	2211	96.43%	Uge 40 - 2018 0.964285714285714		1	20.00%	2334
3	Uge 40 - 2018	2212	100.00%	Uge 40 - 2018 1		2	43.33%	2486
4	Uge 40 - 2018	2229	83.33%	Uge 40 - 2018 0.833333333333333		3	47.06%	2402	2480
5	Uge 40 - 2018	2230	86.96%	Uge 40 - 2018 0.869565217391304		4	47.92%	2201
6	Uge 40 - 2018	2231	94.00%	Uge 40 - 2018 0.94		5	50.00%	2076	2365
7	Uge 40 - 2018	2242	95.74%	Uge 40 - 2018 0.957446808510638		6	51.85%	2428
8	Uge 40 - 2018	2243	96.36%	Uge 40 - 2018 0.963636363636364		7	53.13%	2230
9	Uge 40 - 2018	2258	91.55%	Uge 40 - 2018 0.915492957746479		8	56.25%	2428
10	Uge 40 - 2018	2259	93.42%	Uge 40 - 2018 0.934210526315789		9	57.58%	2480
11	Uge 40 - 2018	2223	100.00%	Uge 40 - 2018 1		10	58.14%	2430
12	Uge 40 - 2018	2224	97.06%	Uge 40 - 2018 0.970588235294118
13	Uge 40 - 2018	2225	100.00%	Uge 40 - 2018 1
14	Uge 40 - 2018	2235	96.20%	Uge 40 - 2018 0.962025316455696
15	Uge 40 - 2018	2236	94.59%	Uge 40 - 2018 0.945945945945946
16	Uge 40 - 2018	2237	91.46%	Uge 40 - 2018 0.914634146341464
17	Uge 40 - 2018	2317	92.86%	Uge 40 - 2018 0.928571428571429
18	Uge 40 - 2018	2318	71.43%	Uge 40 - 2018 0.714285714285714
Sheet

 

[Bilingual]

Hi AhoyNC, thanks for your tries, however i need the calculation for the Overview sheet, unfortunately.

 

[AhoyNC]

I think this should do what you want. I wasn't clear at first where you were trying to put the data.
This is an array formula and must be entered with CTRL-SHIFT-ENTER. Drag formulas down as needed.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Uge 40 - 2018		Uge 41 - 2018		Uge 42 - 2018		Uge 43 - 2018		Uge 44 - 2018		Uge 45 - 2018		Uge 46 - 2018		Uge 47 - 2018
2	Top 10	VL	Pct.	VL	Pct.	VL	Pct.	VL	Pct.	VL	Pct.	VL	Pct.	VL	Pct.	VL	Pct.
3	1	2402	47.06%	2486	43.33%	2420	69.23%	2450	59.32%	2076	50.00%	2365	50.00%	2428	60.61%	2334	20.00%
4	2	2428	51.85%	2480	47.06%	2480	74.19%	2486	59.46%	2304	60.38%	2230	53.13%	2365	66.67%	2210	72.97%
5	3	2480	57.58%	2201	47.92%	2244	74.36%	2402	64.10%	2230	62.71%	2428	65.79%	2303	72.00%	2318	74.19%
6	4	2432	59.46%	2428	56.25%	2402	74.36%	2230	66.22%	2239	64.00%	2323	68.42%	2450	72.58%	2317	75.00%
7	5	2486	62.96%	2430	58.14%	2239	78.16%	2303	68.75%	2323	64.00%	2478	73.53%	2309	74.24%	2313	75.00%
8	6	2446	63.27%	2309	61.29%	2466	79.41%	2239	69.66%	2476	67.50%	2450	73.77%	2484	77.50%	2428	75.86%
9	7	2450	66.04%	2476	65.63%	2309	79.71%	2244	70.00%	2438	70.73%	2239	74.70%	2324	78.13%	2336	76.92%
10	8	2247	67.74%	2336	66.67%	2201	80.00%	2247	72.31%	2444	71.67%	2434	75.00%	2213	78.43%	2303	77.27%
11	9	2420	68.42%	2244	67.86%	2247	80.39%	2210	72.73%	2406	72.50%	2201	76.27%	2210	79.73%	2309	78.26%
12	10	2239	69.05%	2438	68.75%	2482	80.56%	2323	73.58%	2315	73.17%	2476	78.26%	2317	81.67%	2356	80.00%
13	11	2244	70.00%	2450	70.18%	2318	81.58%	2320	73.68%	2328	73.17%	2308	78.43%	2239	81.93%	2312	80.00%
14	12	2318	71.43%	2406	70.59%	2428	82.05%	2440	73.81%	2450	75.00%	2434	79.07%	2310	82.35%	2406	81.25%
15	13	2249	71.67%	2402	70.83%	2319	82.98%	2410	74.07%	2430	76.92%	2319	79.31%	2426	83.93%	2213	82.14%
16	14	2306	73.33%	2444	71.70%	2250	83.33%	2315	74.36%	2213	78.00%	2269	80.00%	2322	84.21%	2319	83.33%
17	15	2484	74.19%	2210	73.33%	2269	83.64%	2482	75.00%	2420	78.13%	2209	80.25%	2307	84.31%	2265	83.78%
Sheet