Once created, the summary table should automatically update to show new values and counts when data changes.
The article below walks through several options, from simple to very advanced.
The more advanced options show how to sort the table in descending order by count.
Manual formula
Note that it is possible to build a summary table with formulas manually.
Pivot table
Another good approach is to use aPivot Table.
Also, the summary table can be easily sorted in a Pivot Table.
Nevertheless, a pivot table is an excellent way to create a summary table.
This is the solution shown in the worksheet at the top of the page.
When source data changes, both formulas will stay in sync.
In general this is a good, simple option.
However, there are limitations.
All-in-one formula
A more advanced solution involves an all-in-one formula.
One approach looks like this:
In this formula, we are using theCHOOSE functiontocombine two arrays.
If you attempt to use the formula above on an in-memoryarray, you’ll get an error.
Note: with slight changes, theBYROW functioncould be used instead of SCAN to produce the same result.
Also note that CHOOSE is again standing in forHSTACKuntil HSTACK is available.
Values can be sorted by one or more columns.
SORT returns a dynamic array of results.
