This is how easy it is now: =GROUPBY(HSTACK(TSales[Person],TEXT(TSales[Date],"mmm, yyy")),TSales[Sales],SUM) GROUPBY is brand new. It is in beta, but it should be released to all of M 365 very soon.
Oh my God this is so complicated😭but I'm not giving up! I will spend some time to catch up on all the small formulas inside the LET function to fully understand it. I believe they will help me solve many problems I currently have. Thank you Mike for sharing!
Wow! Great! LET is fantastic. I use it a lot now. Able to do a lot more with dynamic arrays & so much easier to navigate complex formulas. A lot of fun in this one, Mike. Thanks!
Good grief, this is awesome! My head is almost exploding at the complexity of it! But I can already see how I can use this in my job. I wonder if the inventor of the spreadsheet ever dreamed that this would be possible one day?! Many thanks, Mike Superb 😊
I do not think that Briklin and Frankston ever envisioned this. They could have, though, as there were not foreign to programming. But Briklin and Frankston were to modern day Excel with Dynamic Spilled Arrays like 1974 Mike "BMXISFUN" Girvin was to modern day freestyle BMX. When Briklin and Frankston invented the spreadsheet, they were so busy inventing the basics of the greatest tool ever invented, that they had no capacity to see the outer edges of what was possible today with Single Cell Reports and Dynamic Spilled Array with LAMBDAs and BYROW formulas. When I was there at the birth of BMX, I was so busy just trying to push the limits of riding off jumps and turn the front wheel, that I had no capacity to see the BMX freestyle of today where they can fly out of vertical walls and do multiple flips and turns. Whatever moment in history we may be born in, we see what there is today and that becomes our starting point to look into the future. So all the Excel pros born today see Dynamic Spilled Arrays as the starting point, and when they look into the future, they see things that we cannot dream of. Kinda cool : )
Hi Mike, you are a real Excel guru and your MVP title is definitely deserved, but honestly, I do not know anyone who would do that in this pretty complicated way :-)
I do not do it this way, but there are many analysts that love LET and LAMBDA because they can make custom functions to use over and over. The people who tend to use this are programmers, who look at it like they are writing a program they can use over and over.
Brilliant, I’m definitely going to practice using the let function. Having the ability to define variables like this is great and will make explaining complex formulas much easier, great job showing this, really good video!!👍👍
@@excelisfun Could you maybe do a video that explains the LET function from basics to more advanced? In the past I've found these kind of video of yours incredibly helpful whenever there's a new function. Thank you very much for all the help you give to us Excel users!
Thanks Mike :-) The beauty of the variables in the formula in all its glory :-))))) I have a different perspective on this issue, so I approach it in a different way....i will send you a file with my approach (I think that two variables will be enough.) :-)))
Always thought that it would have been awesome if Excel had the ability to define a variable within a function to use it several times and avoid repetition! You did a great job adding editing to the video with arrows, tips & explanations to follow your logic, I'd say you did just at the right speed for an advanced user that would be using dynamic arrays in the first place. However, I would've added in the beginning some comment/explanation that you are doing this intentionally overly complex to showcase how far you can go with LET() if you wanted to. In reality this was like creating a VBA program in a single cell, which created problems of its own like you using ALT + ENTER to visually keep up with the complexity as there is lacking visibility to the mess if it's all back-to-back, checking each variable after creating it which was like verifying/debugging code, etc. Not something 99% of people would do unless a niche scenario where you don't want macro-enabled workbooks and/or absolutely need auto-refreshing pivots. Cool stuff
Hi Mike very useful video and very practical need everyone for every project ......very big thanks to share with us. Very great ful for us...👌👌👌👌👌👌👌👌💐💐💐💐🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂
Mike, I still have a headache, but this is a great video. Do you have a simpler video to explain the Let function? If so, could you direct me to it? Again, many many thanks for your videos. Simply put, they are most awesome!!!!
The advantage of a Pivot-Table is that it only once has a view on each item ("each item gets put into its 'drawer' at calculation time", with n addings only). The Pivot replacement "n SUMIFS" use exponential calculation resources with n² addings and mostly non-addings. In short: PIVOT memorizes already added items, SUMIFS does not. In theory, the LET approach would need an iteration on a Person/Month sorted basis of the values to reach Pivot-Table's strength.
Thank you for your ready Mr. Mike Please keep sharing your priceless videos. May God bless you.. Stay healthy and stay safe in this turbulence time. All greenlights to you. With love and respect Arijit
All of it: so awesome, but for me choose (yes, we’ve seen you use it before) for its capability to mash columns together, and switch for its capability to fill individual positions in an array, really drew my attention and admiration. WE are gonna use that ‘profusely’. I’ll make a demo video now to show you how I would explain topics like these (we are gonna use LET so much in the future...). I’m gonna break the mould in my video, please let me know what you think about it - stay tuned.
@@excelisfun Thanks for the feedback - autocorrect typo: vote => video. I corrected it. My format needs some extensive set-up, but it allows me to explain things in an even more elegant way, I believe. As soon as it airs, have a look. If you like it and you want me to, I’ll explain it to (just) you.
This is how easy it is now: =GROUPBY(HSTACK(TSales[Person],TEXT(TSales[Date],"mmm, yyy")),TSales[Sales],SUM) GROUPBY is brand new. It is in beta, but it should be released to all of M 365 very soon.
If you don't have GROUPBY yet, this is with HSTACK and VSTACK: =LET(d,TSales[Date],p,TSales[Person],s,TSales[Sales], dmin,MIN(d),dstart,DATE(YEAR(dmin),MONTH(dmin),1), m,MONTH(d),mul,UNIQUE(m),mc,COUNT(mul), pul,SORT(UNIQUE(p)),pc,COUNTA(pul), rec,pc*mc,RecSeq,SEQUENCE(rec,,0), pcr,INDEX(pul,INT(RecSeq/mc)+1), mseq,MOD(RecSeq,mc),sdcr,EDATE(dstart,mseq),edcr,EOMONTH(dstart,mseq), totals,SUMIFS(s,p,pcr,d,">="&sdcr,d,"
Yesss, beautiful. I'm a little disappointed though. I expected you to include the header in the formula too! It's great to do these things as thought experiments though. I've come to the conclusion that SUMIFS doesn't work properly in Dynamic Arrays. Consider a different way to address your problem (which I did!) below. I take the table of data and add the month column to it, creating a "data including month" table. I then create a unique table of just the person and month columns, which also works great. I then attempt to use a column and row sequence to sum the data in the "data including month" table from the "unique" table. Excel gets confused by this and assumes that the sequence that we are summing is actually the sequence that we want to output. Thus it returns the exact number of rows in the data inc month table, overlaying the unique data, which obviously gives a bunch of values and NAs. It's very strange, I always assumed that Excel could easily determine output by the "Criteria" data, not the Criteria Range and Sum Range data. SUMIFS is clearly not getting it!!! Perhaps I'll sue Microsoft. That should help... =LET(data,TSales,r,ROWS(data),c,COLUMNS(data),d,INDEX(data,,1),mnth,EOMONTH(VALUE(d),0), seqAll,SEQUENCE(r,c+1),seqR,SEQUENCE(r),seqC,SEQUENCE(,c+1,0), dataIncMonth,IF((seqAll*0)+seqC=0,INDEX(mnth,seqR,1),INDEX(data,seqR,seqC)), requiredCols,INDEX(dataIncMonth,seqR,SEQUENCE(,2,3,-3)), uniqueData,UNIQUE(requiredCols),uR,ROWS(uniqueData),uC,COLUMNS(uniqueData), seqU,SEQUENCE(uR,uC+1),seqUr,SEQUENCE(uR),seqUc,SEQUENCE(,uC+1), comment,"works fine so far!!!!", uniqueDataIncSumIfs,IF((seqU*0)+seqUc=uC+1,SUMIFS(INDEX(dataIncMonth,,4),INDEX(dataIncMonth,,2),INDEX(uniqueData,seqUr,1),INDEX(dataIncMonth,,1),INDEX(uniqueData,seqUr,2)),INDEX(uniqueData,seqUr,seqUc)), uniqueDataIncSumIfs)
Hi Rico, impressive that you do experiments, kudos to that, tried to figure it out what went wrong. General idea is , as long as SUMIFS (COUNTIFS also)has range argument and not array argument in it's syntax I am afraid we can not trick them to work. Range expects a range range, should exist in the workbook, see it like raw data that has an address not a PObox address,if you create an array as a result to a calculation but it does not exist physically in the workbook , it will not take it. INDEX does not transform an array to a range (only if it has 2 indexes and a ":", other topic). INDEX was good to trick array arguments before, for not using ctrl shift enter. In memory you can create any array but if it doesn´t live in workbook real estate sumifs will error it. You can not receive post mail on your Facebook address. Back to your formula, first SUMIFS in your formula has as range an INDEX(dataINcMonth...and dataIncMonth is declared as an IF((seq....which is an array calc...so...value error. So if you create a additional column , should be as a traditional helper column on the real estate of the spreadsheet, if is in memory , you can not trick it to work. For that we have MMULT. Is my 2 cent opinion.
@@Excelambda yes, the range/array issue is indeed the problem. I don't like it! I'm surprised that Microsoft didn't consider it when producing their filter and unique functions. The UNIQUE function, in my mind, should have tried to replicate the select statement in SQL, thus you could use: UNIQUE(person,MONTH(date),SUM(sales)). It seems that we're always going to be doing multiple formulas where one could suffice in returning a table. All good fun though. Most of my work seems to be in power pivot these days anyway, so theoretical examples on RUclips just keep my eye in!
I originally had headers in formula. But what is the point, I thought. The values are hard coded into formula, so why not just hard code them into cell. Easier, I thought.
@@ricos1497 Ha-ha, indeed , is so easy for us to imagine what we need , dear excel, I need a function ExcelFunction(My_Problem)=Result. We have to give them credit for what they do, appreciate and deal with what we have, and hope for the best with patience and understanding. We can not be grateful enough for LET function and all the dynamic arrays, is the living proof that they are on the right track. It reshaped and reset the whole excel thing. I am starting to define universal LET formulas for certain task and use them in other LET formulas, LET in LET, as powerful as almost VBA. For that I can ignore SUMIFS and many other bugs. 😊
@@ricos1497 and cr gr0912, yes, no arrays in ranges in the IFS and SUMIF and COUNTIF all the way back to the beginning. People have been screaming mad and wanting to sue (like you said) always. It is just a range and we are stuck with that... : (
Question. At 5:52 you name a new variable, rows, and then create the value for rows. But ROWS() is a function, itself. So Excel isn't fooled, and determines rows is not ROWS() because it is within the name12 parameter? So in the same manner, I could name a variable sum or sumproduct and this would not confuse LET()?
Great video! Is it possible to use this concept to build similar style reports with cube functions. Say, I have a matrix with multi number of row cube members, thus creating multi subtotal and total rows? Thank you for your awesome videos!
I am months and months behind... : ( With covid and too much normal job work, I am very behind. WHen the book does finally come out, it will have some good stuff, but now it probably won't be out till mid to late next year : ( : ( : (
I used below one. =LET(qq,SORT(UNIQUE(HSTACK(TSales[Person],BYROW(TSales[Date],LAMBDA(R,EOMONTH(R,0))))),{1,2}),rr,MAP(CHOOSECOLS(qq,1),CHOOSECOLS(qq,2),LAMBDA(aa,bb,SUM(FILTER(TSales[Sales],(TSales[Person]=aa)*(MONTH(TSales[Date])=MONTH(bb)*(YEAR(TSales[Date])=YEAR(bb))))))),VSTACK(HSTACK(qq,rr),HSTACK("","Grand Total",SUM(rr))))
www.spreadsheetsmadeeasy.com/assign-button-macro/ It would be better if this was on a ribbon though so you can use it for any spreadsheet. support.microsoft.com/en-us/office/assign-a-macro-to-a-button-728c83ec-61d0-40bd-b6ba-927f84eb5d2c You would need to know a little VBA to accomplish this as well. You can start by recording a macro that goes to the next sheet and creates a new one. You would then have to alter this code to make it work how you want. This is where you would need to know some VBA or how to look it up.
Magic! But what chance would somebody else looking at the spreadsheet have of working out/auditing what it is doing? Sometimes it may be better to go "old fashioned" with a separate formula for each column.
I modified my formula to add the totals and I made it so if you added a new year it would sort correctly =LET(d,SalesTeamates[Date],n,SalesTeamates[Person]&" "&TEXT(DATE(YEAR(d),MONTH(d),1),"yyy-m"),u,UNIQUE(SORT(n)),s,MMULT(--(u=TRANSPOSE(n)),SalesTeamates[Sales]),r,ROWS(s),SWITCH(SEQUENCE(r+1,2),r*2+1,"Grand Total",r*2+2,SUM(s),CHOOSE({1,2},u,s)))
Don't like to be a scrooge, but the link being http instead of https caused Chrome to not download it without two approvals. Haven't seen the video yet, but I have no doubt it's great like they ALL are!
Below was how I solved it: Thank you there's a lot to learn from you. =LET(M,CHOOSECOLS(SORTBY(SalesTable,SalesTable[Person],1,SalesTable[Date],1),2,1,3), D,EOMONTH(CHOOSECOLS(M,2),0),E,DATE(YEAR(D),MONTH(D),1), N,UNIQUE(HSTACK(CHOOSECOLS(M,1),E,D)), Total,SUMIFS(SalesTable[Sales],SalesTable[Person],CHOOSECOLS(N,1),SalesTable[Date],">="&CHOOSECOLS(N,2),SalesTable[Date],"
![[TF2] The Russian Bear Trap](http://i.ytimg.com/vi/YFNSwz2_rOA/mqdefault.jpg)
ok this is easily can be replaced with 3 months of learning excel functions, thanks for the clear and cohesive explanation
This is how easy it is now:
=GROUPBY(HSTACK(TSales[Person],TEXT(TSales[Date],"mmm, yyy")),TSales[Sales],SUM)
GROUPBY is brand new. It is in beta, but it should be released to all of M 365 very soon.
Thanks for this spectacular lesson !! This file give us tons of new tips to learn !!!!
You are welcome!!!
Wow! My head hurts but that was fantastic! RUclips only allows me one thumbs up, so this is the best I can offer. 👍👍
Thanks Mike
Thank you soooooooooo much for the 10 Thumbs Ups in the act of one thumbs-uping : ) : )
many thanks Mike, you are the best!
Glad this helps!!!!
LET is an absolute beast of a function
Great tutorial
I like the way you put it: beast!!!!!
Oh my God this is so complicated😭but I'm not giving up! I will spend some time to catch up on all the small formulas inside the LET function to fully understand it. I believe they will help me solve many problems I currently have. Thank you Mike for sharing!
Celia, it is complicated for me too!!!! But it is worth fighting to get through it : ) Go, Celia!!!
Wow! Great! LET is fantastic. I use it a lot now. Able to do a lot more with dynamic arrays & so much easier to navigate complex formulas. A lot of fun in this one, Mike. Thanks!
You are welcome, Jim!!! Great to hear that you are using LET more.
Good grief, this is awesome! My head is almost exploding at the complexity of it! But I can already see how I can use this in my job.
I wonder if the inventor of the spreadsheet ever dreamed that this would be possible one day?!
Many thanks, Mike
Superb 😊
I do not think that Briklin and Frankston ever envisioned this. They could have, though, as there were not foreign to programming. But Briklin and Frankston were to modern day Excel with Dynamic Spilled Arrays like 1974 Mike "BMXISFUN" Girvin was to modern day freestyle BMX. When Briklin and Frankston invented the spreadsheet, they were so busy inventing the basics of the greatest tool ever invented, that they had no capacity to see the outer edges of what was possible today with Single Cell Reports and Dynamic Spilled Array with LAMBDAs and BYROW formulas. When I was there at the birth of BMX, I was so busy just trying to push the limits of riding off jumps and turn the front wheel, that I had no capacity to see the BMX freestyle of today where they can fly out of vertical walls and do multiple flips and turns.
Whatever moment in history we may be born in, we see what there is today and that becomes our starting point to look into the future. So all the Excel pros born today see Dynamic Spilled Arrays as the starting point, and when they look into the future, they see things that we cannot dream of. Kinda cool : )
@@excelisfun Cool indeed!
Hi Mike, you are a real Excel guru and your MVP title is definitely deserved, but honestly, I do not know anyone who would do that in this pretty complicated way :-)
I do not do it this way, but there are many analysts that love LET and LAMBDA because they can make custom functions to use over and over. The people who tend to use this are programmers, who look at it like they are writing a program they can use over and over.
Mindblowing! Awesome! 👍👍
Thanks, Teammate : )
Brilliant, I’m definitely going to practice using the let function. Having the ability to define variables like this is great and will make explaining complex formulas much easier, great job showing this, really good video!!👍👍
Glad you like it and find value with it, Marcus!!!
@@excelisfun Could you maybe do a video that explains the LET function from basics to more advanced? In the past I've found these kind of video of yours incredibly helpful whenever there's a new function. Thank you very much for all the help you give to us Excel users!
You are an Excel Genius guy.Thanks god i Don't teach Excel,I just teach Simple - Simple Finance calculations.This is very Big stuff.
but we are still on a Team together : )
@@excelisfun Yes sir : ).
LET there be an amazing solution! Great job Mike!
Thanks, Chris : ) : ) : ) : )
Holy Cow. That's so amazing Mike. Thank you so much.
You are welcome so much, fellow teacher Syed MM : ) : )
Thanks Mike :-) The beauty of the variables in the formula in all its glory :-)))))
I have a different perspective on this issue, so I approach it in a different way....i will send you a file with my approach (I think that two variables will be enough.) :-)))
WHAT!?!?!?! two, as in 2 variables : ) Cool! I can't wait to check it out and post it in the downloadable workbook : ) Thanks, Teammate!!!
@@excelisfun Could you share the file?
@@GenePatNic Yes, I will add it right next to your solution in the download workbook when I get it : )
Thanks, O Masterful One : ) : ) I have added your example to the download workbook : )
@@GenePatNic I added his two variable solution to download workbook : )
This is a spectacular video. What a nice formula. Awesome Mike 👌
Thank you Mike. Looks complicated but I will learn how to use the LET function.
It is sort of complicated. Not for all situations, that is for sure. But does have some good uses : )
Astonishing!!!!!! Sir
Glad you like it, umesh!!!
Always thought that it would have been awesome if Excel had the ability to define a variable within a function to use it several times and avoid repetition!
You did a great job adding editing to the video with arrows, tips & explanations to follow your logic, I'd say you did just at the right speed for an advanced user that would be using dynamic arrays in the first place. However, I would've added in the beginning some comment/explanation that you are doing this intentionally overly complex to showcase how far you can go with LET() if you wanted to.
In reality this was like creating a VBA program in a single cell, which created problems of its own like you using ALT + ENTER to visually keep up with the complexity as there is lacking visibility to the mess if it's all back-to-back, checking each variable after creating it which was like verifying/debugging code, etc. Not something 99% of people would do unless a niche scenario where you don't want macro-enabled workbooks and/or absolutely need auto-refreshing pivots. Cool stuff
Good show
Glad you like it, Excel Teammate!
Boom!Super Cool Lesson With The Awesome LET Function...Thank You Mike :)
You are Boom!!!!!! Welcome!!!!!! darryl : )
Thanks!
Awesome lesson, inspire me to do a lot of tables & analysis with let function
Thank you very much, Rigo V!!!!!! Your support helps me to keep making videos for our Awesome Online Excel Team!!!!!
Yes, LET has helped with formula analysis so much. It makes our formulas much more efficient in calculation time.
Hi Mike
very useful video and very practical need everyone for every project ......very big thanks to share with us.
Very great ful for us...👌👌👌👌👌👌👌👌💐💐💐💐🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂
You are welcome for the share, ashish!!!! : ) : ) : ) : )
Wow. So much to learn here.
Thanks for the tips.
You are welcome, Tim!
Another Crazy formula construction, this needs a while to understand, thank👍👍👍
Thanks for boomerang back again, Statistics Master Ogwal : )
@@excelisfun I'm back nw, I'm glad you're safe from covid-19
@@ogwalfrancis Yes. I hope you are safe too : )
@@excelisfun We're still partly in lockdown here in Uganda, East Africa because some activities are still locked down but I'm safe
@@excelisfun We're still partly in lockdown here in Uganda, East Africa because some activities are still locked down but I'm safe
Amazing is an understatement!!!! :) :) Thanks Mike.
You are welcome, John "Formula Guy" Borg!!!!!!!
thanks you so much sir very nice
You are welcome so much, Kamlesh!!!!
Mike, I still have a headache, but this is a great video. Do you have a simpler video to explain the Let function? If so, could you direct me to it? Again, many many thanks for your videos. Simply put, they are most awesome!!!!
Thanks
You are welcome!
So good, thank you, every video I learn something new. :)
Glad you can learn new things, Davor!!!!
Thanks,
Great crazy formula with many tips and tricks.
I suggest in time 08:05 for pcr use roundup(seq/mec,) it's easier.
Thanks for that tip : )
That’s amazing.
Glad you like it : )
The advantage of a Pivot-Table is that it only once has a view on each item ("each item gets put into its 'drawer' at calculation time", with n addings only).
The Pivot replacement "n SUMIFS" use exponential calculation resources with n² addings and mostly non-addings.
In short: PIVOT memorizes already added items, SUMIFS does not.
In theory, the LET approach would need an iteration on a Person/Month sorted basis of the values to reach Pivot-Table's strength.
That was great Mike, thanks.
You are welcome, Nigel!!!
Brilliant Mike
Glad you like it, James!!!
Thumbs up of course!
Great video. Do you have a tutorial where a subtotal would be applied after each change in person using let ?
Thank you for the wonderful video...!!
You are welcome for the wonderful video, Arijit!!!!!
Thank you for your ready Mr. Mike
Please keep sharing your priceless videos. May God bless you..
Stay healthy and stay safe in this turbulence time.
All greenlights to you.
With love and respect
Arijit
@@arijitsen7980 Thank you for the love and respect. Greenlight = I'll keep making more Excel videos : )
All of it: so awesome, but for me choose (yes, we’ve seen you use it before) for its capability to mash columns together, and switch for its capability to fill individual positions in an array, really drew my attention and admiration. WE are gonna use that ‘profusely’.
I’ll make a demo video now to show you how I would explain topics like these (we are gonna use LET so much in the future...).
I’m gonna break the mould in my video, please let me know what you think about it - stay tuned.
What do you mean when you wrote: "I’ll make a demo vote now"?
I can't wait for your video : )
@@excelisfun Thanks for the feedback - autocorrect typo: vote => video. I corrected it.
My format needs some extensive set-up, but it allows me to explain things in an even more elegant way, I believe.
As soon as it airs, have a look. If you like it and you want me to, I’ll explain it to (just) you.
@@GeertDelmulle I will be happy to watch and learn from your posted video : ) I can't wait!!!
Thank you for the video
You are welcome, Thahazeeb!!!
Good content
Glad you like it, Md Raj : ) : ) : ) : )
With the release of new formulas such as Hstack and Vstack, are there any changes you would makento this formula?
This is how easy it is now:
=GROUPBY(HSTACK(TSales[Person],TEXT(TSales[Date],"mmm, yyy")),TSales[Sales],SUM)
GROUPBY is brand new. It is in beta, but it should be released to all of M 365 very soon.
If you don't have GROUPBY yet, this is with HSTACK and VSTACK:
=LET(d,TSales[Date],p,TSales[Person],s,TSales[Sales],
dmin,MIN(d),dstart,DATE(YEAR(dmin),MONTH(dmin),1),
m,MONTH(d),mul,UNIQUE(m),mc,COUNT(mul),
pul,SORT(UNIQUE(p)),pc,COUNTA(pul),
rec,pc*mc,RecSeq,SEQUENCE(rec,,0),
pcr,INDEX(pul,INT(RecSeq/mc)+1),
mseq,MOD(RecSeq,mc),sdcr,EDATE(dstart,mseq),edcr,EOMONTH(dstart,mseq),
totals,SUMIFS(s,p,pcr,d,">="&sdcr,d,"
Yesss, beautiful. I'm a little disappointed though. I expected you to include the header in the formula too!
It's great to do these things as thought experiments though. I've come to the conclusion that SUMIFS doesn't work properly in Dynamic Arrays. Consider a different way to address your problem (which I did!) below. I take the table of data and add the month column to it, creating a "data including month" table. I then create a unique table of just the person and month columns, which also works great. I then attempt to use a column and row sequence to sum the data in the "data including month" table from the "unique" table. Excel gets confused by this and assumes that the sequence that we are summing is actually the sequence that we want to output. Thus it returns the exact number of rows in the data inc month table, overlaying the unique data, which obviously gives a bunch of values and NAs. It's very strange, I always assumed that Excel could easily determine output by the "Criteria" data, not the Criteria Range and Sum Range data. SUMIFS is clearly not getting it!!! Perhaps I'll sue Microsoft. That should help...
=LET(data,TSales,r,ROWS(data),c,COLUMNS(data),d,INDEX(data,,1),mnth,EOMONTH(VALUE(d),0),
seqAll,SEQUENCE(r,c+1),seqR,SEQUENCE(r),seqC,SEQUENCE(,c+1,0),
dataIncMonth,IF((seqAll*0)+seqC=0,INDEX(mnth,seqR,1),INDEX(data,seqR,seqC)),
requiredCols,INDEX(dataIncMonth,seqR,SEQUENCE(,2,3,-3)),
uniqueData,UNIQUE(requiredCols),uR,ROWS(uniqueData),uC,COLUMNS(uniqueData),
seqU,SEQUENCE(uR,uC+1),seqUr,SEQUENCE(uR),seqUc,SEQUENCE(,uC+1),
comment,"works fine so far!!!!",
uniqueDataIncSumIfs,IF((seqU*0)+seqUc=uC+1,SUMIFS(INDEX(dataIncMonth,,4),INDEX(dataIncMonth,,2),INDEX(uniqueData,seqUr,1),INDEX(dataIncMonth,,1),INDEX(uniqueData,seqUr,2)),INDEX(uniqueData,seqUr,seqUc)),
uniqueDataIncSumIfs)
Hi Rico, impressive that you do experiments, kudos to that, tried to figure it out what went wrong. General idea is , as long as SUMIFS (COUNTIFS also)has range argument and not array argument in it's syntax I am afraid we can not trick them to work. Range expects a range range, should exist in the workbook, see it like raw data that has an address not a PObox address,if you create an array as a result to a calculation but it does not exist physically in the workbook , it will not take it. INDEX does not transform an array to a range (only if it has 2 indexes and a ":", other topic). INDEX was good to trick array arguments before, for not using ctrl shift enter.
In memory you can create any array but if it doesn´t live in workbook real estate sumifs will error it. You can not receive post mail on your Facebook address. Back to your formula, first SUMIFS in your formula has as range an INDEX(dataINcMonth...and dataIncMonth is declared as an IF((seq....which is an array calc...so...value error. So if you create a additional column , should be as a traditional helper column on the real estate of the spreadsheet, if is in memory , you can not trick it to work. For that we have MMULT. Is my 2 cent opinion.
@@Excelambda yes, the range/array issue is indeed the problem. I don't like it! I'm surprised that Microsoft didn't consider it when producing their filter and unique functions. The UNIQUE function, in my mind, should have tried to replicate the select statement in SQL, thus you could use:
UNIQUE(person,MONTH(date),SUM(sales)). It seems that we're always going to be doing multiple formulas where one could suffice in returning a table. All good fun though. Most of my work seems to be in power pivot these days anyway, so theoretical examples on RUclips just keep my eye in!
I originally had headers in formula. But what is the point, I thought. The values are hard coded into formula, so why not just hard code them into cell. Easier, I thought.
@@ricos1497 Ha-ha, indeed , is so easy for us to imagine what we need , dear excel, I need a function ExcelFunction(My_Problem)=Result. We have to give them credit for what they do, appreciate and deal with what we have, and hope for the best with patience and understanding. We can not be grateful enough for LET function and all the dynamic arrays, is the living proof that they are on the right track. It reshaped and reset the whole excel thing. I am starting to define universal LET formulas for certain task and use them in other LET formulas, LET in LET, as powerful as almost VBA. For that I can ignore SUMIFS and many other bugs. 😊
@@ricos1497 and cr gr0912, yes, no arrays in ranges in the IFS and SUMIF and COUNTIF all the way back to the beginning. People have been screaming mad and wanting to sue (like you said) always. It is just a range and we are stuck with that... : (
Question. At 5:52 you name a new variable, rows, and then create the value for rows. But ROWS() is a function, itself. So Excel isn't fooled, and determines rows is not ROWS() because it is within the name12 parameter? So in the same manner, I could name a variable sum or sumproduct and this would not confuse LET()?
How to add subtotal witch multiple criteria, example based on columns persons and month, thanks mate
Great video! Is it possible to use this concept to build similar style reports with cube functions. Say, I have a matrix with multi number of row cube members, thus creating multi subtotal and total rows? Thank you for your awesome videos!
How to fill down blank cells with the cells value above using let function
, great
Glad it is great for you, SHAHOO!!!!
This is some tuff stuff. Thanks for the hand! When does your new book come out?
I am months and months behind... : ( With covid and too much normal job work, I am very behind. WHen the book does finally come out, it will have some good stuff, but now it probably won't be out till mid to late next year : ( : ( : (
I used below one. =LET(qq,SORT(UNIQUE(HSTACK(TSales[Person],BYROW(TSales[Date],LAMBDA(R,EOMONTH(R,0))))),{1,2}),rr,MAP(CHOOSECOLS(qq,1),CHOOSECOLS(qq,2),LAMBDA(aa,bb,SUM(FILTER(TSales[Sales],(TSales[Person]=aa)*(MONTH(TSales[Date])=MONTH(bb)*(YEAR(TSales[Date])=YEAR(bb))))))),VSTACK(HSTACK(qq,rr),HSTACK("","Grand Total",SUM(rr))))
Can we add a button in an excel worksheet that take the user to the next sheet if it's available or else create a new one?
www.spreadsheetsmadeeasy.com/assign-button-macro/
It would be better if this was on a ribbon though so you can use it for any spreadsheet.
support.microsoft.com/en-us/office/assign-a-macro-to-a-button-728c83ec-61d0-40bd-b6ba-927f84eb5d2c
You would need to know a little VBA to accomplish this as well. You can start by recording a macro that goes to the next sheet and creates a new one. You would then have to alter this code to make it work how you want. This is where you would need to know some VBA or how to look it up.
@@t.pigeon2384 thanks
Magic! But what chance would somebody else looking at the spreadsheet have of working out/auditing what it is doing? Sometimes it may be better to go "old fashioned" with a separate formula for each column.
Until the auditors know how to use LET, that is true ; )
I am an accountant who can do auditing, so now I have a new job : )
@@excelisfun I am a semi-retired accountant who was SO HAPPY to give up auditing oh so many moons ago :-}}
@@henryg5735 Accounting is fun!!!
@@excelisfun It can be (eg I love problem solving), but oh my you made me laugh!!!
I guess I am stupid. This is so complicated. Pivot table seems a lot easier
I modified my formula to add the totals and I made it so if you added a new year it would sort correctly
=LET(d,SalesTeamates[Date],n,SalesTeamates[Person]&" "&TEXT(DATE(YEAR(d),MONTH(d),1),"yyy-m"),u,UNIQUE(SORT(n)),s,MMULT(--(u=TRANSPOSE(n)),SalesTeamates[Sales]),r,ROWS(s),SWITCH(SEQUENCE(r+1,2),r*2+1,"Grand Total",r*2+2,SUM(s),CHOOSE({1,2},u,s)))
Love it! I added it to the workbook!
That’s one long LET formula. Looks more like a theorem.
I guess: LET(theorem) ; )
Don't like to be a scrooge, but the link being http instead of https caused Chrome to not download it without two approvals. Haven't seen the video yet, but I have no doubt it's great like they ALL are!
Thank you. I fixed it. I need help from teammates like you when things are broken. Go Team!!!!
Below was how I solved it: Thank you there's a lot to learn from you.
=LET(M,CHOOSECOLS(SORTBY(SalesTable,SalesTable[Person],1,SalesTable[Date],1),2,1,3),
D,EOMONTH(CHOOSECOLS(M,2),0),E,DATE(YEAR(D),MONTH(D),1),
N,UNIQUE(HSTACK(CHOOSECOLS(M,1),E,D)),
Total,SUMIFS(SalesTable[Sales],SalesTable[Person],CHOOSECOLS(N,1),SalesTable[Date],">="&CHOOSECOLS(N,2),SalesTable[Date],"