digest
Short Summary
Main recommendation: Build a single-cell Excel formula that uses LET, UNIQUE, SORT, CHOOSE, SUMIFS, and INDEX to produce a dynamic, sorted report of unique date-and-product combinations with their summed sales, updating automatically as the source table changes. The video notes this only works in Microsoft 365, since earlier versions cannot run it, and that pivot tables were the traditional alternative. The formula is credited to Excel Wizard, a commenter who posted it under the previous video (Excel Magic Trick 1671).
Key Points
- UNIQUE on the date and product columns produces every unique date-product combination, spilling from a single top cell.
- SORT, using an array argument of the first and second columns, orders the results by date and then by product.
- LET defines the sorted result as a variable (named "u") so the formula can reference it several times without repeating it, which the speaker says is unique to Microsoft 365's LET function.
- CHOOSE joins three columns (date, product, sales) into one three-column table.
- SUMIFS calculates the sales total for each date-product row, using INDEX to pull the matching criteria columns from the variable.
Details Worth Noting
- Concrete instructions:
- Enter UNIQUE in the top cell with the date and product columns as its array, and format the output column as a date.
- In the top cell of the next area, wrap the UNIQUE result in SORT, using the column index numbers for the first and then the second column (in array syntax) as sort keys.
- Wrap the whole expression in LET, define a variable (the video names it u) for the sorted result, and place the final formula after the variable definition.
- Use CHOOSE with index numbers 1, 2, 3 to combine the date column, product column, and SUMIFS sales column. For the first two columns, the video uses INDEX on the variable to select all rows of the first column, then repeats the variable reference in CHOOSE as a placeholder, because CHOOSE only handles one column per argument.
- Build SUMIFS with the sales column as sum range, the date column and product column as criteria ranges, and INDEX-pulled columns as criteria, then close SUMIFS, CHOOSE, and LET.
- Add conditional formatting with a formula rule using a relative reference to the first cell of the output range and the test "not equal to empty string" (""), then apply fill and borders, so the grayed-out spilled range is visually cleaned up.
- Apply currency formatting to the sales column.
- The video demonstrates that copying new records below the source table updates the report automatically.
- The video mentions that the spilled array may grow or shrink, which is why conditional formatting is needed to handle the changing range.
- The speaker says the reason CHOOSE accepts the repeated variable reference is not fully understood, but that CHOOSE only processes single columns at a time.
Bottom Line
A single LET-based formula can replace a pivot table for this sales report in Microsoft 365, but it requires careful use of conditional formatting to keep the dynamic output readable.
