Channel: ExcelIsFun

Category: Education

Tags: formulas and functionsexcel magic trick 1780cross tabulated reporthighline collegemike girvinemtvstack functionformula cross tab reportemt 1780excel is funexcel magicadd total row to spilled arrayexcel array formulahstack array functionpivottablehstackexcel functionsexcel funhstack functionexcelsingle cell reportsvstack array functionvstackmicrosoft 365 excelexcel 365excel formulasdynamic spilled array formulasexcelisfun

Description: Download Excel File: 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