Where data is the named range B5:B104. The result is the two-column table in E5:F14, with values sorted in descending order by count.
Get unique values
The first step in this problem is to get a list of unique colors from the data. This is easy to do with the UNIQUE function: Since there 23 unique colors in B5:B104, UNIQUE returns an array containing 23 color names:
Count unique values
Now that we have a list of values, the next step is to get a count for each unique value. This can be done with the COUNTIF function like this: Here, the UNIQUE function returns the unique values in the data as the criteria argument, and COUNTIF calculates a count for each value. The result is an array with 23 counts like this: We now have the basic ingredients we need to solve the problem.
Combine values and counts
The next step is to combine the list of unique colors with the counts to form the two-column table seen in column E and F. This can be done with the HSTACK function, which is designed to combine arrays horizontally. We can use HSTACK like this: The result from HSTACK is a list of 23 unique colors on the left, combined with 23 counts on the right:
We are getting close to a solution, but we still need to reorder the list to show the highest counts first, and drop all but the top 10 counts.
Sort by count
To sort the table by count, we can use the SORT function like this: Now we have the values sorted by count in descending order:
Top 10 values by count
The final step is to remove all but the top 10 values. The easiest way to do this is with the TAKE function, which is designed to extract rows and columns from arrays. In this case, we want the first 10 rows, so we provide 10 for rows: The screen below shows the output of this formula:
Optimize
The formula above works fine, but it is a bit inefficient, since UNIQUE values are calculated twice. This might impact performance in larger sets of data. To streamline the formula, we can use the LET function. The LET function is used to declare and assign values to variables. In this case, we can use LET like this: Here, we use UNIQUE to get unique values and assign the result to a variable named u. Then we replace UNIQUE(data) with u where it occurs in the formula. The result is that UNIQUE values are calculated just one time.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
