Download Excel File: https://excelisfun.net/files/EMT1781.xlsm
These two functions were released in March 16, 2022 and are in Beta at that time.
Learn about the new HSTACK and VSTACK functions to Create Single Cell Cross Tabulated Report with Total Rows.
Topics:
1. (00:00) Introduction
2. (00:37) Video Link for Old Version of Formula
3. (00:54) Caveat: PivotTables are Easier. But if you want solution to instantly update, then Formulas are the only method.
4. (01:14) Concept Trick to make the complicated formula easier to understand.
5. (02:14) LET Function to build variable inputs and deliver the final report.
6. (03:13) Create unique lists for employees and products with SORT, UNIQUE and TRANSPOSE functions.
7. (03:50) How to look at each part of formula when building a formula with the LET function.
8. (04:20) Build First Row of Report using HSTACK function.
9. (05:39) Build Second Row of Report with HSTCK.
10. (05:43) SUMIFS to create sum of sales for product by employee.
11. (07:22) SUMIFS to create sum of sales for employee.
12. (08:05) Build Third Row of Report with HSTACK and SUMIFS.
13. (09:18) Create final report with VSTACK.
14. (09:46) Test formula by adding new data.
15. (10:21) Conditional formatting updates when new data is added.
16. (10:57) Summary
17. (11:09) Closing and Video Links