Channel: ExcelIsFun

Category: Education

Tags: formulas and functionschoosecols functionhighline collegeexcel magic trick 1787mike girvinemtsingle cell dynamic spilled arrya formulasemt 1787singel cell reportsexcel is funexcel magichstack array functionexcel functionsexcel fundynamic spiled array formulaslet functionexcel array formulasmonth sales by sales rep reportexcelvstack array functiondefined namesmicrosoft 365 excelexcel 365excel formulaslambda functionexcelisfun

Description: Download Excel File: excelisfun.net/files/EMT1787.xlsx Learn how to create single cell reports in the Excel worksheet with multiple functions, including re-usable LAMBDA functions. Topics: 1. (00:00) Introduction 2. (00:37) Approach to creating formula 3. (01:08) LET Function with Variables 4. (02:00) Create Block of Report Conditions with the functions: EOMONTH, HSTACK, UNIQUE and SORT 5. (02:20) EOMONTH Function Trick 6. (03:46) HSTACK Function for middle of report 7. (04:25) UNIQUE function to create a unique combination of all sum calculation conditions 8. (04:50) SORT report conditions by two columns 9. (05:25) CHOSECOLS and EOMONTH functions to create end of month and begin of month conditions for adding month sales amounts 10. (07:17) SUMIFS to adding month sales for each sale rep 11. (09:34) Why we use the LET function 12. (10:28) HSTACK to create inside of report with conditional sum totals 13. (10:57) VSTACK function to create the full vertical report with headers, mid-section of report and Grand Total Row 14. (12:13) HSTACK to create Grand Total Row 15. (13:12) Add new data to check if report is dynamic 16. (13:21) Review of functions used in formula 17. (13:43) LAMBDA function to create re-usable function 18. (16:55) Test the new LAMBDA function 19. (17:47) Closing and Video Links