combine SUMIF with SMALL

Bilingual

Board Regular
Joined
Oct 1, 2010
Messages
186
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
ABC
1???
2Top 10Uge 40 - 2018Uge 41 - 2018
3147%43%
4252%47%
5358%48%
6459%56%
7563%58%
8663%61%
9766%66%
10868%67%
11968%68%
121069%69%
Sheet
 
Upvote 0
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.
 
Upvote 0
Like this?
Excel Workbook
AB
1
2Top 10
31Uge 41 - 2018
42Uge 40 - 2018
53Uge 40 - 2018
64Uge 41 - 2018
75Uge 40 - 2018
86Uge 41 - 2018
97Uge 40 - 2018
108Uge 41 - 2018
119Uge 40 - 2018
1210Uge 41 - 2018
Sheet
 
Upvote 0
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.
 
Upvote 0
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
ABCDEFGHIJ
1PeriodItemPercentageCombinedRankPercentageItem(s)
2Uge 40 - 2018221196.43%Uge 40 - 2018 0.964285714285714120.00%2334
3Uge 40 - 20182212100.00%Uge 40 - 2018 1243.33%2486
4Uge 40 - 2018222983.33%Uge 40 - 2018 0.833333333333333347.06%24022480
5Uge 40 - 2018223086.96%Uge 40 - 2018 0.869565217391304447.92%2201
6Uge 40 - 2018223194.00%Uge 40 - 2018 0.94550.00%20762365
7Uge 40 - 2018224295.74%Uge 40 - 2018 0.957446808510638651.85%2428
8Uge 40 - 2018224396.36%Uge 40 - 2018 0.963636363636364753.13%2230
9Uge 40 - 2018225891.55%Uge 40 - 2018 0.915492957746479856.25%2428
10Uge 40 - 2018225993.42%Uge 40 - 2018 0.934210526315789957.58%2480
11Uge 40 - 20182223100.00%Uge 40 - 2018 11058.14%2430
12Uge 40 - 2018222497.06%Uge 40 - 2018 0.970588235294118
13Uge 40 - 20182225100.00%Uge 40 - 2018 1
14Uge 40 - 2018223596.20%Uge 40 - 2018 0.962025316455696
15Uge 40 - 2018223694.59%Uge 40 - 2018 0.945945945945946
16Uge 40 - 2018223791.46%Uge 40 - 2018 0.914634146341464
17Uge 40 - 2018231792.86%Uge 40 - 2018 0.928571428571429
18Uge 40 - 2018231871.43%Uge 40 - 2018 0.714285714285714
Sheet
 
Upvote 0
Hi AhoyNC, thanks for your tries, however i need the calculation for the Overview sheet, unfortunately.
 
Upvote 0
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
ABCDEFGHIJKLMNOPQ
1Uge 40 - 2018Uge 41 - 2018Uge 42 - 2018Uge 43 - 2018Uge 44 - 2018Uge 45 - 2018Uge 46 - 2018Uge 47 - 2018
2Top 10VLPct.VLPct.VLPct.VLPct.VLPct.VLPct.VLPct.VLPct.
31240247.06%248643.33%242069.23%245059.32%207650.00%236550.00%242860.61%233420.00%
42242851.85%248047.06%248074.19%248659.46%230460.38%223053.13%236566.67%221072.97%
53248057.58%220147.92%224474.36%240264.10%223062.71%242865.79%230372.00%231874.19%
64243259.46%242856.25%240274.36%223066.22%223964.00%232368.42%245072.58%231775.00%
75248662.96%243058.14%223978.16%230368.75%232364.00%247873.53%230974.24%231375.00%
86244663.27%230961.29%246679.41%223969.66%247667.50%245073.77%248477.50%242875.86%
97245066.04%247665.63%230979.71%224470.00%243870.73%223974.70%232478.13%233676.92%
108224767.74%233666.67%220180.00%224772.31%244471.67%243475.00%221378.43%230377.27%
119242068.42%224467.86%224780.39%221072.73%240672.50%220176.27%221079.73%230978.26%
1210223969.05%243868.75%248280.56%232373.58%231573.17%247678.26%231781.67%235680.00%
1311224470.00%245070.18%231881.58%232073.68%232873.17%230878.43%223981.93%231280.00%
1412231871.43%240670.59%242882.05%244073.81%245075.00%243479.07%231082.35%240681.25%
1513224971.67%240270.83%231982.98%241074.07%243076.92%231979.31%242683.93%221382.14%
1614230673.33%244471.70%225083.33%231574.36%221378.00%226980.00%232284.21%231983.33%
1715248474.19%221073.33%226983.64%248275.00%242078.13%220980.25%230784.31%226583.78%
Sheet
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

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
Back
Top