{"id":3178,"date":"2021-04-01T10:09:11","date_gmt":"2021-04-01T17:09:11","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3178"},"modified":"2021-04-01T10:09:12","modified_gmt":"2021-04-01T17:09:12","slug":"top-10-with-formulas-in-excel","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3178","title":{"rendered":"Top 10 With Formulas in Excel"},"content":{"rendered":"\n<p>A top 10 list is a common form for displaying information, especially on dashboards and summarized reports.&nbsp; It is easy to create the list when working with sorted data, just cell link to the top 10 items in the list\u2026 easy!&nbsp; It\u2019s also relatively simple when using Auto Filter, Tables and Pivot Tables, as it is a default filter setting within these features.&nbsp; However, when creating a top 10 with formulas on a non-sorted dataset, things become a little bit tricky. In this post, I will show you exactly how to do it.&nbsp; Through these methods, you\u2019re not restricted to a top 10; you can create a top 5, top 8, or any number you choose.<\/p>\n\n\n\n<p>Some of the most common formula problems with top 10 lists are:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Dealing with duplicate values<\/li><li>Working with categories<\/li><li>How to change it to get the bottom 10<\/li><\/ul>\n\n\n\n<p>Don\u2019t worry, we will cover all of these (they are also all included in the example file).&nbsp; Hopefully, along the way, you\u2019ll learn a little more about how Excel works.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Dynamic arrays<\/h4>\n\n\n\n<p>Users with an Office 365 subscription now have access to a group of functions, which make use of the new dynamic array calculation engine.&nbsp; These give us a nice simple way to calculate a top 10.&nbsp; So, if you have a dynamic array enabled version of Excel, then be sure to check out that section.<\/p>\n\n\n\n<p><strong>Download the example file<\/strong><\/p>\n\n\n\n<p>I recommend you download the example file for this post.&nbsp; Then you\u2019ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.<\/p>\n\n\n\n<p><a href=\"https:\/\/exceloffthegrid.com\/download\/8877\/\" target=\"_blank\" rel=\"noreferrer noopener\"><\/a><br>Download the file:&nbsp;<a href=\"https:\/\/exceloffthegrid.com\/download\/8877\/\" target=\"_blank\" rel=\"noreferrer noopener\">0015 Top 10 using formulas.zip<\/a><\/p>\n\n\n\n<p><strong>Note: If you don\u2019t have a Dynamic Array enabled version of Excel, the tabs containing those examples will display errors.&nbsp;<\/strong><\/p>\n\n\n\n<p><strong>Watch the video:<\/strong><\/p>\n\n\n\n<p><a href=\"https:\/\/www.youtube.com\/watch?v=FJJKk-FldUY\" target=\"_blank\" rel=\"noreferrer noopener\">Watch the video on YouTube<\/a><\/p>\n\n\n\n<p>Contents<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Traditional functions<\/h3>\n\n\n\n<p>We are going to start with traditional functions (i.e., non-dynamic array).&nbsp; Using these, it is easy to obtain the top 10 values.&nbsp; But getting the names\/labels which relate to those ten values is the challenge.<\/p>\n\n\n\n<p>Start by looking at the&nbsp;<strong>Top 10 \u2013 non-DA<\/strong>&nbsp;tab of the example file.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Using the LARGE function<\/h3>\n\n\n\n<p>In our example file, there are 26 customers, with their values.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/02\/Top-10-source-scenario.png?ezimgfmt=rs:657x661\/rscb2\/ng:webp\/ngcb2\" alt=\"Top 10 source scenario\" class=\"wp-image-8782\"\/><\/figure>\n\n\n\n<p>If we wanted to create a top 10 of these customers (without sorting the list), we could use the LARGE function.&nbsp; Cell G4 contains this formula:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=LARGE($C$2:$C$27,ROW(F4)-ROW(F$3))<\/pre>\n\n\n\n<p>LARGE only has two arguments:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=LARGE(Range, k)<\/pre>\n\n\n\n<ul class=\"wp-block-list\"><li>Range \u2013 The range of data to be analyzed.<\/li><li>k \u2013 The n<sup>th<\/sup>&nbsp;item to be found.<\/li><\/ul>\n\n\n\n<p>In our example, the data range to analyze is cells C2 to C27.<\/p>\n\n\n\n<p>The k value is calculated as the row number, minus the row number of the header row.&nbsp; This always calculates the relative row position in a range of cells.&nbsp; The first row of data calculates as 1, the second row, calculates as 2, and so on.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">ROW(F4)-ROW(F$3)<\/pre>\n\n\n\n<p>The formula in cell G4 has been copied down to display the top 10 values.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/02\/Top-10-copied-down-LARGE-function.png?ezimgfmt=rs:607x379\/rscb2\/ng:webp\/ngcb2\" alt=\"\" class=\"wp-image-8783\"\/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Finding the labels for the top 10<\/h3>\n\n\n\n<p>We have the values, so now we can calculate the customer name for that value.<\/p>\n\n\n\n<p>We can\u2019t use VLOOKUP as the customer name is to the left of the lookup value.&nbsp; Instead, we will use the INDEX\/MATCH formula combination.&nbsp; Cell F4, in our example, would contain the following formula.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=INDEX($A$2:$A$27,MATCH(G4,$C$2:$C$27,0))<\/pre>\n\n\n\n<p>If this formula were copied down into cells F5 \u2013 F13.&nbsp; Our worksheet would display as follows:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/02\/Top-10-Duplicate-Values.png?ezimgfmt=rs:607x358\/rscb2\/ng:webp\/ngcb2\" alt=\"Top 10 Duplicate Values\" class=\"wp-image-8784\"\/><\/figure>\n\n\n\n<p>There is one huge problem \u2013 our top 10 values are not unique; there are 3 values of 80 (see the screenshot above).&nbsp; A basic INDEX\/MATCH only returns the first value, so it finds the name&nbsp;<strong>Alfa<\/strong>&nbsp;3 times.&nbsp; Yet,&nbsp;<strong>Echo<\/strong>&nbsp;and&nbsp;<strong>Tango<\/strong>, who also have 80, do not feature on the list.&nbsp; This is clearly an error, so how do we get around this?<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Finding the labels when the top 10 values are not unique<\/h3>\n\n\n\n<p>To solve the issue of finding labels with non-unique values, we will turn to an advanced array formula.&nbsp; The formula in cell F4 should be:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">{=INDEX($A$2:$A$27,SMALL(IF($C$2:$C$27=G4,ROW($C$2:$C$27)-ROW($C$1)),\nCOUNTIF($G$4:G4,G4)))}<\/pre>\n\n\n\n<p>Woah!!! That\u2019s big and complicated!.<\/p>\n\n\n\n<p><strong>This is a special type of formula, known as an array formula.&nbsp; Don\u2019t put the curly brackets ( {&nbsp; } ) at the start or the end when typing the formula into the formula bar; when you press Ctrl+Shift+Enter, Excel will add these itself.&nbsp; Pressing Ctrl+Shift+Enter lets Excel know that it is an array formula.<\/strong><strong>REMEMBER! \u2013 If you go back into an array formula to edit it, you need to press Ctrl + Shift + Enter again to re-enter the formula.<\/strong><\/p>\n\n\n\n<p>This formula is like INDEX\/MATCH, but returns the 1st, 2nd, 3rd, 4th\u2026 or nth value.&nbsp; Let\u2019s dig a bit deeper to understand how it works.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Section 1 \u2013 IF function<\/h4>\n\n\n\n<p>In the middle of the formula, we find an IF function.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">IF($C$2:$C$27=G4,ROW($C$2:$C$27)-ROW($C$1))<\/pre>\n\n\n\n<p>In English, this says:<\/p>\n\n\n\n<p><em>If C2 = G4 then return the count of rows between C2 and C1.<\/em><\/p>\n\n\n\n<p>As this is an array formula, it automatically goes onto the next row and calculates again<\/p>\n\n\n\n<p><em>If C3 = G4 then return the count of rows between C3 and C1.<\/em><\/p>\n\n\n\n<p>And it keeps on going<\/p>\n\n\n\n<p><em>If C4 = G4 then return the count of rows between C4 and C1.<\/em><\/p>\n\n\n\n<p>This will go all the way from C2 to C27.<\/p>\n\n\n\n<p>For the formula in F4, the IF function calculates as:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;<strong>7<\/strong>;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;\nFALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}<\/pre>\n\n\n\n<p>The 7th item in the list matches cell G4; therefore, the only value which is not FALSE is 7.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Section 2 \u2013 SMALL and COUNTIF functions<\/h4>\n\n\n\n<p>If we feed this into the SMALL function, it would be as follows:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>SMALL(<\/strong>{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;7;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;\nFALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}<strong>,\nCOUNTIF($G$4:G4,G4))<\/strong><\/pre>\n\n\n\n<p>SMALL finds the n<sup>th&nbsp;<\/sup>smallest value.&nbsp; It works in a similar way to LARGE, but for the smallest value.<\/p>\n\n\n\n<p>SMALL only has two arguments:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=SMALL(Range,k)<\/pre>\n\n\n\n<ul class=\"wp-block-list\"><li>Range \u2013 The range of data to be analyzed.<\/li><li>k \u2013 The n<sup>th<\/sup>&nbsp;item to be found.<\/li><\/ul>\n\n\n\n<p>In this context, COUNTIF calculates how many instances of the value have appeared in the top 10 already.<\/p>\n\n\n\n<p>For the first row, there is only 1 item in the top 10 with a score of 120, so COUNTIF will calculate as 1.<\/p>\n\n\n\n<p>The SMALL will find the first smallest value, which is&nbsp;<strong>7<\/strong>, as all the other results are FALSE.<\/p>\n\n\n\n<p>Finally, let\u2019s wrap that in the INDEX function to find the 7th value in the source table.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>=INDEX($A$2:$A$27,<\/strong>7<strong>)<\/strong><\/pre>\n\n\n\n<p>This calculates to cell A8, which in our example is&nbsp;<strong>Golf<\/strong>.<\/p>\n\n\n\n<p>All the workings above were to show how the formula works.&nbsp; Now we can copy the complete formula down into cells F5 \u2013 F13.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Testing on duplicate values<\/h4>\n\n\n\n<p>The first row does not have a duplicate value.&nbsp; So, let\u2019s test it out with cell F11.&nbsp; There are 3 values in the top 10 which are all 80; they are found in G10, G11 and G12.&nbsp; The label in F11 should be&nbsp;<strong>Echo<\/strong>, as it is the 2nd value of 80.<\/p>\n\n\n\n<p>The formula in F11 is:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">{=INDEX($A$2:$A$27,SMALL(IF($C$2:$C$27=G11,ROW($C$2:$C$27)-ROW($C$1)),\nCOUNTIF($G$4:G11,G11)))}<\/pre>\n\n\n\n<p>The IF portion of the function calculates as:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">{<strong>1<\/strong>;FALSE;FALSE;FALSE;<strong>5<\/strong>;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;\nFALSE;FALSE;FALSE;FALSE;FALSE;FALSE;<strong>20<\/strong>;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}<\/pre>\n\n\n\n<p>Rows 1, 5, and 20 in the source table all match the value of 80.<\/p>\n\n\n\n<p>The COUNTIF calculates to 2, which makes sense, as it is the 2nd matching value we are looking for.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><strong>SMALL(<\/strong>{1;FALSE;FALSE;FALSE;5;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;\nFALSE;FALSE;FALSE;FALSE;FALSE;FALSE;20;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},<strong>2)<\/strong><\/pre>\n\n\n\n<p>As the second smallest number is 5, the INDEX function returns the 5th value in the source list, which is&nbsp;<strong>Echo.<\/strong><\/p>\n\n\n\n<p>Fantastic stuff, right!!!<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Bottom 10 values<\/h3>\n\n\n\n<p>If you want the bottom 10 values, the only change is that the formula to get the values uses SMALL, rather than LARGE.<\/p>\n\n\n\n<p>Look at the example file.&nbsp; The formula in cell G18 is:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=SMALL($C$2:$C$27,ROW(F18)-ROW($F$17))<\/pre>\n\n\n\n<p>The only difference between cell G18 and cell G4 is the use of the SMALL function.<\/p>\n\n\n\n<p>The formula in cell F18 is the same as we saw above before (but pointing at different cells).<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=INDEX($A$2:$A$27,SMALL(IF($C$2:$C$27=G18,ROW($C$2:$C$27)-ROW($C$1)),\nCOUNTIF($G$18:G18,G18)))<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Adding criteria<\/h3>\n\n\n\n<p>Sometimes we want to calculate the top 10 results where a specific condition is met.&nbsp; In our example file, look at the&nbsp;<strong>Top 10 \u2013 non-DA with criteria<\/strong>&nbsp;tab.<\/p>\n\n\n\n<p>Cell J4 contains the name of the city; London or Birmingham.&nbsp; The top 10 will only be returned for customers in that city.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/02\/Top-10-with-criteria.png?ezimgfmt=rs:800x667\/rscb2\/ng:webp\/ngcb2\" alt=\"Top 10 with criteria\" class=\"wp-image-8789\"\/><\/figure>\n\n\n\n<p>Cell G4 has the following function:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">{=LARGE(<strong>IF($B$2:$B$27=$J$4,$C$2:$C$27)<\/strong>,ROW(F4)-ROW(F$3))}<\/pre>\n\n\n\n<p><strong>This is another array formula.&nbsp; Remember, don\u2019t enter the curly brackets, but do press Ctrl+Shift+Enter again.<\/strong><\/p>\n\n\n\n<p>This uses the same logic as we\u2019ve already seen.&nbsp; The IF function checks cells B2 to B27; if the value matches cell J4, the value in C2 to C27 is returned.<\/p>\n\n\n\n<p>When the city is London, the IF calculate as follows:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">{<strong>80<\/strong>;FALSE;FALSE;FALSE;<strong>80<\/strong>;<strong>100<\/strong>;<strong>120<\/strong>;FALSE;<strong>95<\/strong>;FALSE;FALSE;<strong>55<\/strong>;FALSE;FALSE;\n<strong>-35<\/strong>;<strong>30<\/strong>;FALSE;FALSE;FALSE;<strong>80<\/strong>;FALSE;<strong>-20<\/strong>;<strong>75<\/strong>;<strong>-15<\/strong>;FALSE;FALSE}<\/pre>\n\n\n\n<p>By using the LARGE function, only the London values will be returned into the top 10.&nbsp; All the non-London values calculate as FALSE.<\/p>\n\n\n\n<p>We make a similar adjustment to the formula which calculates the customer name (the added section is in bold)<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">={INDEX($A$2:$A$27,SMALL(\nIF(($C$2:$C$27=G4)<strong>*($B$2:$B$27=$J$4)<\/strong>,ROW($C$2:$C$27)-ROW($C$1)),\nCOUNTIF(G4:$G$4,G4)))}<\/pre>\n\n\n\n<p><strong>This is an array formula.&nbsp; Don\u2019t enter the curly brackets.&nbsp; Do press Ctrl+Shift+Enter again.<\/strong><\/p>\n\n\n\n<p>This uses similar TRUE\/FALSE logic to only calculate the values which match the city selected.<\/p>\n\n\n\n<p>Look at the example file (Cells E17 \u2013 H27) if you want to see how a bottom 10 with criteria is calculated.<\/p>\n\n\n\n<p><strong>Generate accurate VBA code in seconds with AutoMacro<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image\"><a href=\"https:\/\/www.automateexcel.com\/vba-code-generator\/?utm_source=200287236\" target=\"_blank\" rel=\"noreferrer noopener\"><img decoding=\"async\" src=\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2019\/11\/AutoMacroExample.gif\" alt=\"AutoMacroExample\" class=\"wp-image-9260\"\/><\/a><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Dynamic array functions<\/h3>\n\n\n\n<p>Everything above seems quite complicated.&nbsp; Wouldn\u2019t it be better if we could use some more straightforward formulas?&nbsp; If you have a dynamic array enabled version of Excel (only available to Office 365 subscribers at the time of writing), then you\u2019re in luck.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">INDEX \/ SORT \/ SEQUENCE function combination<\/h3>\n\n\n\n<p>Look at the<strong>&nbsp;Top 10 DA<\/strong>&nbsp;tab of the example file.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/02\/Dynamic-Arrays-using-SORT.png?ezimgfmt=rs:650x407\/rscb2\/ng:webp\/ngcb2\" alt=\"Dynamic Arrays using SORT\" class=\"wp-image-8790\"\/><\/figure>\n\n\n\n<p>In cell F4 the formula is:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=INDEX(SORT(A2:C27,3,-1),SEQUENCE(10),{1,3})<\/pre>\n\n\n\n<p>And that\u2019s it!&nbsp; No more array formulas required for the top 10, no need to press Ctrl+Shirt+Enter, no need to copy down.<\/p>\n\n\n\n<p>Let\u2019s dig into this a bit deeper.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">SORT<\/h4>\n\n\n\n<p>SORT has four arguments:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=SORT(array, [sort_index], [sort_order], [by_col])<\/pre>\n\n\n\n<ul class=\"wp-block-list\"><li>array: The range of cells, or array of values to be sorted.<\/li><li>[sort_index]: The nth column or row to apply the sort to.&nbsp; For example, to sort by the 3rd column, the sort index would be 3.<\/li><li>[sort_order]: 1 = sort in ascending order, -1 = sort in descending order (if excluded the argument will default to 1).<\/li><li>[by_col]: TRUE = sort by columns, FALSE = sort by rows (if excluded the argument will default to FALSE).<\/li><\/ul>\n\n\n\n<p>In our formula, we used the following SORT.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SORT(A2:C27,3,-1)<\/pre>\n\n\n\n<p>This sorts the cells A2 to C27 on the 3rd column in descending order.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">SEQUENCE<\/h4>\n\n\n\n<p>The SEQUENCE function has four arguments:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=SEQUENCE(Rows, [Columns], [Start], [Step])<\/pre>\n\n\n\n<ul class=\"wp-block-list\"><li>Rows: The number of rows to return<\/li><li>[Columns]: The number of columns to return.&nbsp; If excluded, it will return a single column.<\/li><li>[Start]: The first number in the sequence.&nbsp; If omitted, it will start at 1.<\/li><li>[Step]: The amount to increment each subsequent value.&nbsp; If excluded, each increment will be 1.<\/li><\/ul>\n\n\n\n<p>In our example, the following creates a list from 1 to 10.&nbsp; We only need the first argument, as we can use the default options for the optional arguments.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SEQUENCE(10)<\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">INDEX<\/h4>\n\n\n\n<p>Let\u2019s put both SORT and SEQUENCE into a traditional INDEX function:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=INDEX(SORT(A2:C27,3,-1),SEQUENCE(10),{1,3})<\/pre>\n\n\n\n<p>The formula returns the first 10 results from the SORT and returns columns 1 and 3.<\/p>\n\n\n\n<p>That was so easy\u2026 right.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Bottom 10<\/h3>\n\n\n\n<p>To get the bottom 10, we only need to change the 3rd argument of the SORT function from -1 to 1.&nbsp; Look at cell F18 in the example file to see this in action.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a href=\"https:\/\/www.ezoic.com\/what-is-ezoic\/\" target=\"_blank\" rel=\"noreferrer noopener\"><\/a>report this adDynamic arrays with criteria<\/h3>\n\n\n\n<p>Even if we have criteria to apply, it is still straight forward with dynamic arrays.<\/p>\n\n\n\n<p>Now, look at the&nbsp;<strong>Top 10 \u2013 DA with criteria<\/strong>&nbsp;tab in the example file.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/02\/Dynamic-arrays-with-criteria.png?ezimgfmt=rs:800x389\/rscb2\/ng:webp\/ngcb2\" alt=\"Dynamic arrays with criteria\" class=\"wp-image-8791\"\/><\/figure>\n\n\n\n<p>The formula in Cell F4 is:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=INDEX(SORT(<strong>FILTER(A2:C27,B2:B27=J4)<\/strong>,3,-1),SEQUENCE(10),{1,3})<\/pre>\n\n\n\n<p>The only difference to the previous example is that we are using the FILTER function to only include the matching items, before it is fed into the SORT function.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">FILTER<\/h4>\n\n\n\n<p>FILTER has three arguments:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=FILTER(array, include, [if_empty])<\/pre>\n\n\n\n<ul class=\"wp-block-list\"><li>array: The range of cells, or array of values to filter.<\/li><li>include: An array of TRUE\/FALSE results, where the TRUE values will be retained in the filter.<\/li><li>[if_empty]: The value to display if no rows are returned.<\/li><\/ul>\n\n\n\n<p>In our example, the FILTER function is:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">FILTER(A2:C27,B2:B27=J4)<\/pre>\n\n\n\n<p>It is returning cells A2 to C27, but only where the values from B2 to B27 equals the selected city in cell J4.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Conclusion<\/h3>\n\n\n\n<p>Once you know the techniques and the formulas, calculating a top 10 using formulas in Excel isn\u2019t too bad.<\/p>\n\n\n\n<p>This post demonstrates how good dynamic arrays are; we don\u2019t need to rely on complex array formulas any longer.<\/p>\n\n\n\n<p><strong>Don\u2019t forget:<\/strong><\/p>\n\n\n\n<p>If you\u2019ve found this post useful, or if you have a better approach, then please leave a comment below.<\/p>\n\n\n\n<p>Ref: <a href=\"https:\/\/exceloffthegrid.com\/creating-a-top-10-using-formulas\/\">Top 10 with formulas in Excel &#8211; Excel Off The Grid<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>A top 10 list is a common form for displaying information, especially on dashboards and summarized reports.&nbsp; It is easy to create the list when working with sorted data, just cell link to the top 10 items in the list\u2026 easy!&nbsp; It\u2019s also relatively simple when using Auto Filter, Tables and Pivot Tables, as it <a class=\"read-more\" href=\"https:\/\/SUMMALAI.COM\/?p=3178\">Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,18],"tags":[660],"class_list":["post-3178","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-microsoft-office","tag-top-10-with-formulas-in-excel"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 4.9.8 - aioseo.com -->\n\t<meta name=\"description\" content=\"A top 10 list is a common form for displaying information, especially on dashboards and summarized reports. It is easy to create the list when working with sorted data, just cell link to the top 10 items in the list\u2026 easy! It\u2019s also relatively simple when using Auto Filter, Tables and Pivot Tables, as it\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Summa Lai\"\/>\n\t<meta name=\"keywords\" content=\"top 10 with formulas in excel,microsoft family,microsoft office\" \/>\n\t<link rel=\"canonical\" href=\"https:\/\/SUMMALAI.COM\/?p=3178\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 4.9.8\" \/>\n\t\t<script type=\"application\/ld+json\" class=\"aioseo-schema\">\n\t\t\t{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/?p=3178#article\",\"name\":\"Top 10 With Formulas in Excel | Summa Lai\",\"headline\":\"Top 10 With Formulas in Excel\",\"author\":{\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/?author=2#author\"},\"publisher\":{\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/#person\"},\"image\":{\"@type\":\"ImageObject\",\"url\":\"https:\\\/\\\/exceloffthegrid.com\\\/wp-content\\\/uploads\\\/2020\\\/02\\\/Top-10-source-scenario.png?ezimgfmt=rs:657x661\\\/rscb2\\\/ng:webp\\\/ngcb2\",\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/?p=3178\\\/#articleImage\"},\"datePublished\":\"2021-04-01T10:09:11-07:00\",\"dateModified\":\"2021-04-01T10:09:12-07:00\",\"inLanguage\":\"en-US\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/?p=3178#webpage\"},\"isPartOf\":{\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/?p=3178#webpage\"},\"articleSection\":\"Microsoft Family, Microsoft Office, Top 10 With Formulas in Excel\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/?p=3178#breadcrumblist\",\"itemListElement\":[{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/SUMMALAI.COM#listItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/SUMMALAI.COM\",\"nextItem\":{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/?cat=10#listItem\",\"name\":\"Microsoft Family\"}},{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/?cat=10#listItem\",\"position\":2,\"name\":\"Microsoft Family\",\"item\":\"https:\\\/\\\/SUMMALAI.COM\\\/?cat=10\",\"nextItem\":{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/?cat=18#listItem\",\"name\":\"Microsoft Office\"},\"previousItem\":{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/SUMMALAI.COM#listItem\",\"name\":\"Home\"}},{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/?cat=18#listItem\",\"position\":3,\"name\":\"Microsoft Office\",\"item\":\"https:\\\/\\\/SUMMALAI.COM\\\/?cat=18\",\"nextItem\":{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/?p=3178#listItem\",\"name\":\"Top 10 With Formulas in Excel\"},\"previousItem\":{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/?cat=10#listItem\",\"name\":\"Microsoft Family\"}},{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/?p=3178#listItem\",\"position\":4,\"name\":\"Top 10 With Formulas in Excel\",\"previousItem\":{\"@type\":\"ListItem\",\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/?cat=18#listItem\",\"name\":\"Microsoft Office\"}}]},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/#person\",\"name\":\"sladmin\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/?p=3178#personImage\",\"url\":\"https:\\\/\\\/SUMMALAI.COM\\\/wp-content\\\/uploads\\\/2020\\\/05\\\/Summa_Logo-150x150.png\",\"width\":96,\"height\":96,\"caption\":\"sladmin\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/?author=2#author\",\"url\":\"https:\\\/\\\/SUMMALAI.COM\\\/?author=2\",\"name\":\"Summa Lai\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/?p=3178#authorImage\",\"url\":\"https:\\\/\\\/SUMMALAI.COM\\\/wp-content\\\/uploads\\\/2020\\\/05\\\/Summa_Logo-150x150.png\",\"width\":96,\"height\":96,\"caption\":\"Summa Lai\"}},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/?p=3178#webpage\",\"url\":\"https:\\\/\\\/SUMMALAI.COM\\\/?p=3178\",\"name\":\"Top 10 With Formulas in Excel | Summa Lai\",\"description\":\"A top 10 list is a common form for displaying information, especially on dashboards and summarized reports. It is easy to create the list when working with sorted data, just cell link to the top 10 items in the list\\u2026 easy! It\\u2019s also relatively simple when using Auto Filter, Tables and Pivot Tables, as it\",\"inLanguage\":\"en-US\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/#website\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/?p=3178#breadcrumblist\"},\"author\":{\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/?author=2#author\"},\"creator\":{\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/?author=2#author\"},\"datePublished\":\"2021-04-01T10:09:11-07:00\",\"dateModified\":\"2021-04-01T10:09:12-07:00\"},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/#website\",\"url\":\"https:\\\/\\\/SUMMALAI.COM\\\/\",\"name\":\"Summa Lai\",\"description\":\"Never Stop Learning, Building a Little Wiki...\",\"inLanguage\":\"en-US\",\"publisher\":{\"@id\":\"https:\\\/\\\/SUMMALAI.COM\\\/#person\"}}]}\n\t\t<\/script>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"Top 10 With Formulas in Excel | Summa Lai","description":"A top 10 list is a common form for displaying information, especially on dashboards and summarized reports. It is easy to create the list when working with sorted data, just cell link to the top 10 items in the list\u2026 easy! It\u2019s also relatively simple when using Auto Filter, Tables and Pivot Tables, as it","canonical_url":"https:\/\/SUMMALAI.COM\/?p=3178","robots":"max-image-preview:large","keywords":"top 10 with formulas in excel,microsoft family,microsoft office","webmasterTools":{"miscellaneous":""},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/SUMMALAI.COM\/?p=3178#article","name":"Top 10 With Formulas in Excel | Summa Lai","headline":"Top 10 With Formulas in Excel","author":{"@id":"https:\/\/SUMMALAI.COM\/?author=2#author"},"publisher":{"@id":"https:\/\/SUMMALAI.COM\/#person"},"image":{"@type":"ImageObject","url":"https:\/\/exceloffthegrid.com\/wp-content\/uploads\/2020\/02\/Top-10-source-scenario.png?ezimgfmt=rs:657x661\/rscb2\/ng:webp\/ngcb2","@id":"https:\/\/SUMMALAI.COM\/?p=3178\/#articleImage"},"datePublished":"2021-04-01T10:09:11-07:00","dateModified":"2021-04-01T10:09:12-07:00","inLanguage":"en-US","mainEntityOfPage":{"@id":"https:\/\/SUMMALAI.COM\/?p=3178#webpage"},"isPartOf":{"@id":"https:\/\/SUMMALAI.COM\/?p=3178#webpage"},"articleSection":"Microsoft Family, Microsoft Office, Top 10 With Formulas in Excel"},{"@type":"BreadcrumbList","@id":"https:\/\/SUMMALAI.COM\/?p=3178#breadcrumblist","itemListElement":[{"@type":"ListItem","@id":"https:\/\/SUMMALAI.COM#listItem","position":1,"name":"Home","item":"https:\/\/SUMMALAI.COM","nextItem":{"@type":"ListItem","@id":"https:\/\/SUMMALAI.COM\/?cat=10#listItem","name":"Microsoft Family"}},{"@type":"ListItem","@id":"https:\/\/SUMMALAI.COM\/?cat=10#listItem","position":2,"name":"Microsoft Family","item":"https:\/\/SUMMALAI.COM\/?cat=10","nextItem":{"@type":"ListItem","@id":"https:\/\/SUMMALAI.COM\/?cat=18#listItem","name":"Microsoft Office"},"previousItem":{"@type":"ListItem","@id":"https:\/\/SUMMALAI.COM#listItem","name":"Home"}},{"@type":"ListItem","@id":"https:\/\/SUMMALAI.COM\/?cat=18#listItem","position":3,"name":"Microsoft Office","item":"https:\/\/SUMMALAI.COM\/?cat=18","nextItem":{"@type":"ListItem","@id":"https:\/\/SUMMALAI.COM\/?p=3178#listItem","name":"Top 10 With Formulas in Excel"},"previousItem":{"@type":"ListItem","@id":"https:\/\/SUMMALAI.COM\/?cat=10#listItem","name":"Microsoft Family"}},{"@type":"ListItem","@id":"https:\/\/SUMMALAI.COM\/?p=3178#listItem","position":4,"name":"Top 10 With Formulas in Excel","previousItem":{"@type":"ListItem","@id":"https:\/\/SUMMALAI.COM\/?cat=18#listItem","name":"Microsoft Office"}}]},{"@type":"Person","@id":"https:\/\/SUMMALAI.COM\/#person","name":"sladmin","image":{"@type":"ImageObject","@id":"https:\/\/SUMMALAI.COM\/?p=3178#personImage","url":"https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2020\/05\/Summa_Logo-150x150.png","width":96,"height":96,"caption":"sladmin"}},{"@type":"Person","@id":"https:\/\/SUMMALAI.COM\/?author=2#author","url":"https:\/\/SUMMALAI.COM\/?author=2","name":"Summa Lai","image":{"@type":"ImageObject","@id":"https:\/\/SUMMALAI.COM\/?p=3178#authorImage","url":"https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2020\/05\/Summa_Logo-150x150.png","width":96,"height":96,"caption":"Summa Lai"}},{"@type":"WebPage","@id":"https:\/\/SUMMALAI.COM\/?p=3178#webpage","url":"https:\/\/SUMMALAI.COM\/?p=3178","name":"Top 10 With Formulas in Excel | Summa Lai","description":"A top 10 list is a common form for displaying information, especially on dashboards and summarized reports. It is easy to create the list when working with sorted data, just cell link to the top 10 items in the list\u2026 easy! It\u2019s also relatively simple when using Auto Filter, Tables and Pivot Tables, as it","inLanguage":"en-US","isPartOf":{"@id":"https:\/\/SUMMALAI.COM\/#website"},"breadcrumb":{"@id":"https:\/\/SUMMALAI.COM\/?p=3178#breadcrumblist"},"author":{"@id":"https:\/\/SUMMALAI.COM\/?author=2#author"},"creator":{"@id":"https:\/\/SUMMALAI.COM\/?author=2#author"},"datePublished":"2021-04-01T10:09:11-07:00","dateModified":"2021-04-01T10:09:12-07:00"},{"@type":"WebSite","@id":"https:\/\/SUMMALAI.COM\/#website","url":"https:\/\/SUMMALAI.COM\/","name":"Summa Lai","description":"Never Stop Learning, Building a Little Wiki...","inLanguage":"en-US","publisher":{"@id":"https:\/\/SUMMALAI.COM\/#person"}}]}},"aioseo_meta_data":{"post_id":"3178","title":null,"description":null,"keywords":[],"keyphrases":{"focus":[],"additional":[]},"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":"","og_custom_url":null,"og_article_section":null,"og_article_tags":[],"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":{"id":"#aioseo-article-65a0cb9fb1c0c","slug":"article","graphName":"Article","label":"Article","properties":{"type":"BlogPosting","name":"#post_title","headline":"#post_title","description":"#post_excerpt","image":"","keywords":"","author":{"name":"#author_name","url":"#author_url"},"dates":{"include":true,"datePublished":"","dateModified":""}}},"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"Article","isEnabled":true},"graphs":[]},"schema_type":"default","schema_type_options":"{\"article\":{\"articleType\":\"BlogPosting\"},\"course\":{\"name\":\"\",\"description\":\"\",\"provider\":\"\"},\"faq\":{\"pages\":[]},\"product\":{\"reviews\":[]},\"recipe\":{\"ingredients\":[],\"instructions\":[],\"keywords\":[]},\"software\":{\"reviews\":[],\"operatingSystems\":[]},\"webPage\":{\"webPageType\":\"WebPage\"}}","pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":"-1","robots_max_videopreview":"-1","robots_max_imagepreview":"large","priority":null,"frequency":"default","local_seo":{"businessInfo":{"name":"","urls":{"website":"","aboutPage":"","contactPage":""},"address":{"line1":"","line2":"","zip":"","city":"","state":"","country":""},"contact":{"email":"","phone":"","fax":""},"ids":{"vatID":"","taxID":"","chamberID":""},"payment":{"priceIndication":"","currenciesAccepted":"","methodsAccepted":""},"areaServed":""},"openingHours":{"show":false,"closedLabel":"","open24h":false,"open24hLabel":"","open247":false,"use24hFormat":false,"twoSets":false,"timezone":"","hours":[]}},"breadcrumb_settings":null,"limit_modified_date":false,"ai":null,"created":"2021-03-29 23:34:27","updated":"2024-01-12 05:18:23","seo_analyzer_scan_date":null},"aioseo_breadcrumb":"<div class=\"aioseo-breadcrumbs\"><span class=\"aioseo-breadcrumb\">\n\t\t\t<a href=\"https:\/\/SUMMALAI.COM\" title=\"Home\">Home<\/a>\n\t\t<\/span><span class=\"aioseo-breadcrumb-separator\">&raquo;<\/span><span class=\"aioseo-breadcrumb\">\n\t\t\t<a href=\"https:\/\/SUMMALAI.COM\/?cat=10\" title=\"Microsoft Family\">Microsoft Family<\/a>\n\t\t<\/span><span class=\"aioseo-breadcrumb-separator\">&raquo;<\/span><span class=\"aioseo-breadcrumb\">\n\t\t\t<a href=\"https:\/\/SUMMALAI.COM\/?cat=18\" title=\"Microsoft Office\">Microsoft Office<\/a>\n\t\t<\/span><span class=\"aioseo-breadcrumb-separator\">&raquo;<\/span><span class=\"aioseo-breadcrumb\">\n\t\t\tTop 10 With Formulas in Excel\n\t\t<\/span><\/div>","aioseo_breadcrumb_json":[{"label":"Home","link":"https:\/\/SUMMALAI.COM"},{"label":"Microsoft Family","link":"https:\/\/SUMMALAI.COM\/?cat=10"},{"label":"Microsoft Office","link":"https:\/\/SUMMALAI.COM\/?cat=18"},{"label":"Top 10 With Formulas in Excel","link":"https:\/\/SUMMALAI.COM\/?p=3178"}],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3178","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3178"}],"version-history":[{"count":2,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3178\/revisions"}],"predecessor-version":[{"id":3184,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3178\/revisions\/3184"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3178"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3178"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3178"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}