Single Cell Excel Reporting with LET, LAMBDA, VSTACK, SUMIFS & More, Complete Lesson! EMT 1787

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

[GeertDelmulle]

What a great video!
On the evolution of Excel:
Back in the days of Old School: you’d write crazy long formula’s in order to calculate a beautiful albeit scalar result.
Then you took it to the next level and started writing crazy array formulas to produce beautiful array results.
Then Dynamic Arrays came out and I thought: no more crazy Array formulas. But instantly you corrected me, and oh boy, was I wrong.
Now, you write crazy array formulas and out come entire reports — fully dynamic, of course.
And you know what: those formulas don’t look all that crazy anymore. And the things we can do now used to be unimaginable.
What great Excel times these are, and what a great teacher you are.
Thanks for your guidance. Respect! :-)

[brianxyz]

This is one of those videos that requires multiple viewings to fully appreciate just how powerful Excel has become.

[pathakprathamesh]

What a perfect example to demonstrate how we have entered a whole new Excel world with amazing possibilities... thank you!

[KevinPGA]

WOW, in your last reporting video, I thought to myself it would be cool if you could run a function on a series of data created in memory. I didn’t have the courage to ask. You just answered that question BIG TIME. It’s truly amazing what we can accomplish with an AWESOME coach. Thank you Professor!!! 👍

[mohamedchakroun]

Umbelievable Mike The most progiciel in the world could not do that :-) I love the bonus function with using lamda absolutately amazing

[drsteele]

Awesome. Wow. You brained my damage. Hey, here's a tip from a simpleton. At 12:00 or so, when you click on 'array2' in the tool tip and it highlights array2, you don't need to then precisely and carefully click the parenthesis - all you need do is hit the right arrow on the keyboard and it will move to the right spot for you to add the next comma.

[kebincui]

Awesome👍 and excellent as always. Thanks Mike

[Softwaretrain]

Amazing combination of new functions.
Just I prefer to name each part of formula inside let function even if it's not repeatable since it more readable but I loved this way as well.
Thanks.

[johnborg]

speechless !!!! That's a lot to practice on!!! Thanks Mike. :) :)

[JoseAntonioMorato]

Dear Mike,
I finally managed to make a LAMBDA that behaved like a function, unlike the previous LAMBDA which was simply a defined name.
The previous one, due to the use of the OFFSET volatile function, to include the header, did not turn into a function. With the current LAMBDA, you only need to include the entire table (ST[#All]) to get the desired result.
Here's the updated version of LAMBDA:
=LAMBDA(Table,
LET(Date, INDEX(Table, , 1), Person, INDEX(Table, , 2), Sales, INDEX(Table, , 3),
rh, SORT(UNIQUE(HSTACK(DROP(Person, 1), EOMONTH(--DROP(Date, 1), 0))), {1, 2}),
U, CHOOSECOLS(rh, 2),
VSTACK(
HSTACK(TAKE(Person, 1), TAKE(Date, 1), TAKE(Sales, 1)),
HSTACK(rh, SUMIFS(Sales, Date, ">"&EOMONTH(--U, -1), Date, "<="&U, Person, CHOOSECOLS(rh, 1))),
HSTACK("", "Grand 🤗

[patrickschardt]

It almost feels like cheating that something that used to be extremely complex is now very easy to do

Wonderful tutorial

Sometimes is useful to creat variables for items only used once. It can help if the variable needs to change and for clarity

[rmbo]

Wow, that formula is BRUTAL. Satisfyingly brutal. Yet another awesome video, thank you for sharing!

[vida]

Great step by step lesson! I like LET ability to materialise each variable like F9 in old school formulas

[wayneedmondson]

Wow Mike! Your single cell report formulas are so awesome! Clever, elegant, logical. Simultaeously humbling and inspiring to watch you work. Thanks for the detailed step by step walkthough which helps us mortals learn the tricks and techniques. Respect and admiration for all that you do :)) Thumbs up!!

[richardhay]

Great video. Creating both LET and LAMBDA in one video is a good idea since I have helped some users who have a hard time understanding the distinction. In reality though if I were going to create a single Lamda to generate a single cell report I would skip the LET. Another commenter thought that you were going to use Advanced Formula Environment to create and save Lambda. So did I. I have used it twice ( I dont create many LAMBDAS) and It is great. Much superior to using Defined Name dialog box. Maybe a video on AFE!

[SyedMuzammilMahasanShahi]

Thanks Mike for this EXCELlent video and this one cell formula which is amazing.

[darrylmorgan]

Boom!That Was One Of The Coolest Most Beautiful Formulas I've Ever Seen...Thank You Mike :)

[MichaRatajczyk]

Amazing video. Soon I will forget how to use Pivot table with this combination of new formulas 🙂

[slom]

humbled...again... and happy for it!
Thank you

[chrism]

Mike, I need to take an Excedrin and go lie down for a while after seeing that LET formula lol. Great video as always!