COUNTIFS, GROUPBY & LET Functions: Create Amazing Single Cell Dynamic Spilled Array Report. EMT 1901
Download Excel File: https://people.highline.edu/mgirvin/AllClasses/excelisfun/EMT1901.xlsxLearn 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! Receive SMS online on sms24.me
TubeReader video aggregator is a website that collects and organizes online videos from the YouTube source. Video aggregation is done for different purposes, and TubeReader take different approaches to achieve their purpose.
Our try to collect videos of high quality or interest for visitors to view; the collection may be made by editors or may be based on community votes.
Another method is to base the collection on those videos most viewed, either at the aggregator site or at various popular video hosting sites.
TubeReader site exists to allow users to collect their own sets of videos, for personal use as well as for browsing and viewing by others; TubeReader can develop online communities around video sharing.
Our site allow users to create a personalized video playlist, for personal use as well as for browsing and viewing by others.
@YouTubeReaderBot allows you to subscribe to Youtube channels.
By using @YouTubeReaderBot Bot you agree with YouTube Terms of Service.
Use the @YouTubeReaderBot telegram bot to be the first to be notified when new videos are released on your favorite channels.
Look for new videos or channels and share them with your friends.
You can start using our bot from this video, subscribe now to COUNTIFS, GROUPBY & LET Functions: Create Amazing Single Cell Dynamic Spilled Array Report. EMT 1901
What is YouTube?
YouTube is a free video sharing website that makes it easy to watch online videos. You can even create and upload your own videos to share with others. Originally created in 2005, YouTube is now one of the most popular sites on the Web, with visitors watching around 6 billion hours of video every month.
