Video:How to create an Excel table
The table will now automatically expand or contract as needed.
For this we use theUNIQUE function.
The formula in F5 is:
This is what makes this solution dynamic.
To change the output from vertical to horizontal, we nest the UNIQUE formula in theTRANSPOSE function.
Video:What is an array?
Calculate unique counts
We now have what we need to calculate the counts.
The formula in G5 is:
With COUNTIFS, conditions are entered inrange/criteriapairs.
If rows are deleted from the table, spill ranges contract if needed.
TheLET functionis used to assign all three of these results to the variablescolors,sizes, andcounts.
Then, we use theHSTACKandVSTACKfunctions to assemble the final table.
For more information on LET,this examplewalks through using the LET function in detail.
However, one drawback is that pivot tables need to be refreshed to show the latest data.
Formulas, on the other hand, update instantly when data changes.
Dynamic two-way sum
The example above performs a dynamic two-waycount.
However, it’s possible for you to easily create a dynamic two-waysumwith the same approach.
See this video:How to build a simple summary tableandthis formula.
Dynamic Array Training
Need structured training for dynamic arrays in Excel?
See our course:Dynamic Array Formulas.
Each subsequent array is appended to the bottom of the previous array….
