COUNTIFS, GROUPBY & LET Functions: Create Amazing Single Cell Dynamic Spilled Array Report. EMT 1901

Download Excel File: https://excelisfun.net/files/EMT1901....
Learn how to create single cell dynamic spilled array formula reports. This report will count and list customers that fall into different marketing coupon customer groups. This is an advanced array formula that uses 22 different functions to create a report that seems impossible, but is easy with functions such as COUNTIFS and GROUPBY. Learn about the functions: GROUPBY, COUNTIFS, LET, TOROW, SORT, UNIQUE, COLUMNS, IF, DROP, HSTACK, COUNTA, ARRAYTOTEXT, SEQUENCE, ROWS, EXPAND, TEXTJOIN, TEXTSPLIT, REDUCE, LAMBDA, VSTACK, MAX, and TRANSPOSE. Also, see how to get an array to spill an array of arrays!
Topics:
1. (00:00) Introduction.
2. (00:24) Methods we used back in 2018 to solve this complex reporting task.
3. (00:37) Goal of report: List unique coupon groups with the count and list of customers in each group.
4. (01:27) Fundamental problem with data: We have a customer list and a coupon sales table, but no set of records that list which coupons each customer used. We will have to transform the data into a table of customer coupon usage records.
5. (02:01) Look at tables of data given and final report.
6. (02:40) Formula #1
7. (02:40) LET functions to define variables. Used when formula elements are repeated in complex formula.
8. (03:27) TOROW, UNIQUE and SORT functions to create Store variable.
9. (04:12) How to test the variables that you create in the LET functions.
10. (05:05) Look at the set of records that we must create to solve this problem.
11. (05:18) COLUMNS function to create Count Stores variable.
12. (05:43) COUNTIFS and IF function to create Customer Store Records variable.
13. (07:16) All Excel Logical Functions (IF, IFS, AND, OR, FILTER and many more) interpret any non-zero number as TRUE and zero as FALSE.
14. (08:17) GROUPBY, COUNTA, ARRAYTOTEXT, HSTACK and DROP functions to create Groupby variable that will count the customers in each group.
15. (10:05) How to sort when there are more than one column in the row_fields argument of GROUPBY function by using the field_relationship argument.
16. (11:05) HSTACK function to make multiple calculations in a GROUPBY function report.
17. (12:21) DROP function to drop the annoying default first row labels in a multiple calculation GROUPBY reports.
18. (12:49) SEQUENCE and ROWS functions to create Report First Column variable.
19. (13:42) DROP and EXPAND functions to create Report Middle Column variable.
20. (14:59) TAKE function to create Report Last Column variable.
21. (15:33) TEXTJOIN & TEXTSPLIT functions to split customer names from an 8 row array into an array for each row. This step solves the dynamic spilled array formula problem of spilling an array of arrays.
22. (18:41) TRANSPOSE & HSTACK to create final report!
23. (19:31) REDUCE, LAMBDA, VSTACK, EXPAND, TEXTSPLIT, MAX and DROP functions to split customer names from an 8 row array into an array for each row. This step solves the dynamic spilled array formula problem of spilling an array of arrays.
24. (20:04) What is REDUCE function and what is LAMBDA function?
25. (24:19) Summary
26. (24:31) Closing

#excel #microsoft #excelisfun #excelcourse #groupby #countifs #drop #take #reduce #textsplit #textjoin #rows #dynamicspilledarrays #singlecellreportformulas

#excel #excel365 #excelisfun #highlinecollege #mikegirvin #freeexcellessons #excelformulas #excelfunctions

Complex Report w/ Single Cell Formula: Count & List Customers in Coupon Groups: Impossible? EMT 1901
COUNTIFS & GROUPBY functions to Create Single Cell Dynamic Spilled Array Customer Report! EMT 1901
Not Possible with Excel Formula? Think Again.
Impossible To Do With Excel Formula is Now Possible!