Download Excel File: https://excelisfun.net/files/EMT1707.xlsx
Learn how to get the EOMONTH function to spill all the lower and upper limits for a month sales report. Then we build a single cell spilled sales report for month sales by Sales Rep. Learn about these functions: EOMONTH, CHOOSE, UNIQUE, SORT, SUMIFS, INDEX, IFERROR, SEQUENCE, ROWS, SUM and LAMBDA.
Solution comes from Bill Szysz.

Topics:
1. (00:00) Introduction to Bill Szysz trick.
2. (00:30) EOMONTH Trick to spill a rectangular range of upper and lower limits of dates for SUMIFS function criteria for a monthly sales report.
3. (00:41) Analysis Toolpak Addin Functions, like EOMONTH can spill results. Problem!
4. (01:22) Double Negative to trick EOMONTH.
5. (02:10) Function argument array operation in months argument of EOMONTH.
6. (03:22) CHOOSE to join a two column table with a one column table using array syntax {1,1,2}.
7. (04:45) UNIQUE to get unique set of records.
8. (05:00) LET function to define variables and make formula more efficient by avoiding repetition.
9. (06:24) SORT with [sort_index] argument with array constant {3,1}.
10. (07:00) SUMIFS and INDEX to create summary column for final report.
11. (09:57) CHOOSE to create final three column report.
12. (10:47) INDEX to create errors for total row.
13. (11:48) IFERROR to add total row.
14. (12:03) CHOOSE to create total row.
15. (13:00) Test formula with new data.
16. (13:27) Look at LAMBDA function to create function.
17. (14:21) Summary, Closing and Video Links

Other Related videos:
Basics of SUMIFS & PivotTables for Monthly Sales Report. Excel Magic Trick 1701.
Basics of Excel Spilled Array Formulas to create Monthly Sales Report. Excel Magic Trick 1702.
Single Cell Spilled Reports with Dynamic Total Row & Formatting. Excel Magic Trick 1703.
Introduction to LAMBDA Excel Function. LET & LAMBDA together to make Single Cell Reports. EMT 1706.