Awesome Mike! Thanks for the Monday COUNTIFS/SUMIFS fun. Great reminder on "" criteria as meaning cell is not empty. Love how BYROW makes it easy to spill results. As always, thanks and thumbs up!!
I am getting success with spilling by row of 2D spilled arrays using Subtotal or Countifs using this approach, where A1 is the start of the spilled array, e.g count values greater than 1 : =COUNTIFS(OFFSET(A1,SEQUENCE(ROWS(A1#),,0),,,COLUMNS(A1#)),">1")
yes:) and thats not all, if you ever had filter in or out your data set by List of values you can do it without Power query, make it dynamic, and make it spill. I have a video on how to do it :)
Was knowing it but from now onwards I can assimilate and practice it thoroughly. We can spill the MAX/MIN/SUM/COUNTIF when criteria range is single column
Hi Mike, very good perspective on the formulas. I always say you need to study the behaviour of a formula. I don't know if you seen my comment on 1769. There is method on SUMIFS that I have not seen somebody else does. It is to spill a cumulative or running total with SUMIFS.
@@excelisfun yeah nice one UA-cam. But to be fair, I am a late subscriber to your channel. That video is probably over a year old and I am catching up and still learning new things. Thank you for your intuitive lessons.
Nice and concise explanation of spilling, unspillable, and spilling the unspillable (using the helper functions). As you point out: it’s the aggregators that need the special treatment (for obvious reasons). I wish to add to this: I think it’s important to be able to spill (I like to call it “vectorize”) as many functions and calculations as possible. Reason: the results can be dynamic (in size) and you can easily build upon those dynamic arrays for further calculations. Gives you a lot of dynamic results with a minimum of effort.
Goo point: dynamic is much more flexible. In some cases, where there are a fixed number of items in the spill, then there can be an argument to not spill.
@@excelisfun I like to control the size of the spill by using a parameter. Especially spilling table widths: the first time the (management) users see this: they can’t believe it. But they adapt quite quickly. ;-) but still: dynamic arrays with flexible spill ranges: good & powerful stuff. Love it! :-)
Amazing video, Mike. When the data table is in the form of a dynamic array, I am not able to use this SUMIFS trick. (even with choosecols and byrow functions.)
CHOOSECOLS and BYROWS deliver arrays!!!!!!! arrays can't be used in SUMIFS... But : ) INDEX delivers ranges that SUMIFS can handle. INDEX can lookup rows or columns. like: INDEX(J15:M26,0,2) for lookup second column
@@excelisfun Thanks Mike. The INDEX function worked beautifully. One more addition to the infinite learnings I get from your videos : “it is very important to understand the difference between an 'Array' and 'Range' and their usage in formula".
@@excelisfun It was good i like how you explaine the prompts as think its something a lot of people skip past and kind of teach it like you have to remember it all
@@excelwithmark That is my specialty here at UA-cam for 14 years: Show the hows and the whys. But most in the world do not care about whys. That makes people like you especially smart !!
hello @ExcelIsFun I'm trying to do sumifs in "single cell" with this formula: =LET(A,HSTACK({"Sun";"Mon";"Sun";"Sun";"Mon";"Mon";"Sun"},SEQUENCE(7,1,2,6)),B,CHOOSECOLS(A,1),C,CHOOSECOLS(A,2),D,UNIQUE(B),SUMIFS(C,B,D)) but its not working. can you help me? Desired report is: {"Sun",74;"Mon",66}
The Criteria "" is pure gold...thanks a lot once again Mike
Awesome Mike! Thanks for the Monday COUNTIFS/SUMIFS fun. Great reminder on "" criteria as meaning cell is not empty. Love how BYROW makes it easy to spill results. As always, thanks and thumbs up!!
You are welcome, Wayne!!!!!
Sir you are great teacher... Hats off
You are welcome, Farhan!!!!
GReat Video as always.
well Lambda magic with the best teacher on earth = blindly creating perfect solutions! :)
Just a guy having fun with Excel and the rest of our Team!!!! Go Team!!!!
Lambda's construction seems bit tricky .......but you explained it very well ..thanks a lot ❤️🙌
It is a bit tricky, but sometimes, it is just the ticket ; )
I am getting success with spilling by row of 2D spilled arrays using Subtotal or Countifs using this approach, where A1 is the start of the spilled array, e.g count values greater than 1
: =COUNTIFS(OFFSET(A1,SEQUENCE(ROWS(A1#),,0),,,COLUMNS(A1#)),">1")
Boom!Cool Fun Class On Spill Results...Thank You Mike :)
You are welcome, Bike Brother darryl!!!!
Thanks!
AWS magic trick,
Go Team!!!!!!!
@@msantosh1220 Super Glad that you like the magic : ) : ) : ) : ) : ) : ) : )
Excellent!!! Thak you and 👍:)
You are welcome, Most EXCELlent Teammate ; )
Old school is easier to understand than BYROW and LAMBDA.
That is true.
Thanks Mike. that was a great one. 7 new functions have been released including the LAMBDA :) I have them on my work Comp. :)
Yes!!! They are finally released : ) Go Team!!!!
Achily i use Microsoft 365 from 5 years i find it very useful spatially for 1TB storage :),
Plus the lasts update it programs from Microsoft
Cool!
It would have been so much more intuitive if the programming allowed this ... BYROW(COUNTIFS(B7:C10,D4))
That's the best explanation yet for a reason to actually use Lambda, Byrow and other new functions! 😁
Mike is the professor non pareil
Glad to help you have fun with Excel : )
best channel i have ever seen for excel like best ever indeed
Glad you like it!!!!
Dear, I have bookings data recorded before and after the travel date. Hiw do I get the number of noshow passengers easily!?
Thanks
Excel magic trick 1585
Hello sir,
Suppose i have to start with 3, not 1
Then how its done?
I tried bt didn't work
Realy appreciate if you tell
Awsome, Thanx Mike!
You are welcome!
Very good. Thanks Mike....
You are very welcome, Matt!!!!
Wow ... so we can force excel to generate a spill array .... that's very interesting
yes:) and thats not all,
if you ever had filter in or out your data set by List of values you can do it without Power query, make it dynamic, and make it spill.
I have a video on how to do it :)
Glad you find it interesting, Hussein!!!
@@ExcelInstructor Cool - your videos are great : )
@@excelisfun Thank you, I know my english is crappy, but I try :)
Plus comming this from you is extremly motivating :)
Thank you
Cool, thanks Mike.
You are welcome, Luciano!!!!!
Was knowing it but from now onwards I can assimilate and practice it thoroughly.
We can spill the MAX/MIN/SUM/COUNTIF when criteria range is single column
Absolutely!!!! Criteria argument can handle function argument array operations : )
The link is ok Today 😃
: ) : ) : ) : ) : ) I thought of you when I was making the link. It is good to be on a good Team : )
Nice. Move on..... Sir
Glad you like it, Ubaidilah!!!
Really very good. All these functions, no idea how to use them - you are showing the way. Thanks.
Glad it is really good for you, antique9999!!!!!
I think you are bayGenius in every function in excel 😎, good to see that, thanks Mike
You are welcome, Sevag!!!! I am just a guy having fun with Excel : )
More reasons to get Microsoft 365. Awesome Mike, thanks!
You are awesomely welcome, Chris M!!!!
Awesome Awesome Awesome as always Mike... thank you very much!
You are welcome very much, Edge!!!! : )
Thanks Mike for this EXCELlent video.
You are welcome, Syed MM and Fellow Teacher : ) : ) : )
👍🏻
Glad you like it, Carlos!!!!
Hi Mike, very good perspective on the formulas. I always say you need to study the behaviour of a formula. I don't know if you seen my comment on 1769. There is method on SUMIFS that I have not seen somebody else does. It is to spill a cumulative or running total with SUMIFS.
I don't think I did see comment. UA-cam comments have really been broken for about a year. I do not often get notifications... : (
@@excelisfun yeah nice one UA-cam. But to be fair, I am a late subscriber to your channel. That video is probably over a year old and I am catching up and still learning new things. Thank you for your intuitive lessons.
@@excel_along_the_way I found your comment and replied
Kindly make a sheet excel trile creteria match return value
I am sorry I do not understand what you are asking?
Nice and concise explanation of spilling, unspillable, and spilling the unspillable (using the helper functions).
As you point out: it’s the aggregators that need the special treatment (for obvious reasons).
I wish to add to this: I think it’s important to be able to spill (I like to call it “vectorize”) as many functions and calculations as possible.
Reason: the results can be dynamic (in size) and you can easily build upon those dynamic arrays for further calculations.
Gives you a lot of dynamic results with a minimum of effort.
Goo point: dynamic is much more flexible. In some cases, where there are a fixed number of items in the spill, then there can be an argument to not spill.
@@excelisfun I like to control the size of the spill by using a parameter.
Especially spilling table widths: the first time the (management) users see this: they can’t believe it.
But they adapt quite quickly. ;-)
but still: dynamic arrays with flexible spill ranges: good & powerful stuff.
Love it! :-)
@@GeertDelmulle L-O-V-E to the max : )
Absolutely brilliant Mike. 👏 Thank u for your hard work u always bring the best 👌
Amazing video, Mike. When the data table is in the form of a dynamic array, I am not able to use this SUMIFS trick. (even with choosecols and byrow functions.)
CHOOSECOLS and BYROWS deliver arrays!!!!!!! arrays can't be used in SUMIFS... But : ) INDEX delivers ranges that SUMIFS can handle. INDEX can lookup rows or columns. like: INDEX(J15:M26,0,2) for lookup second column
XLOOKUP will work too.
@@excelisfun Thanks Mike. The INDEX function worked beautifully. One more addition to the infinite learnings I get from your videos : “it is very important to understand the difference between an 'Array' and 'Range' and their usage in formula".
@@ppanigrahi : )
Great video and well explained
Glad you like it, TechWithMark!!!
@@excelisfun It was good i like how you explaine the prompts as think its something a lot of people skip past and kind of teach it like you have to remember it all
@@excelwithmark That is my specialty here at UA-cam for 14 years: Show the hows and the whys. But most in the world do not care about whys. That makes people like you especially smart !!
@@excelisfun Ah thanks dont know if I could be described as smart but inquisitive might be a word haha Thanks again
@@excelwithmark I am not smart either, just hard working with thigs that are fun : ) : ) Go Team!!!!
hello @ExcelIsFun I'm trying to do sumifs in "single cell" with this formula: =LET(A,HSTACK({"Sun";"Mon";"Sun";"Sun";"Mon";"Mon";"Sun"},SEQUENCE(7,1,2,6)),B,CHOOSECOLS(A,1),C,CHOOSECOLS(A,2),D,UNIQUE(B),SUMIFS(C,B,D)) but its not working. can you help me? Desired report is: {"Sun",74;"Mon",66}