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!!
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! :-)
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")
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 RUclips. 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.
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 :)
@@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 RUclips 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 !!
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".
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}
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 : )
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!!!!!
best channel i have ever seen for excel like best ever indeed
Glad you like it!!!!
The Criteria "" is pure gold...thanks a lot once again Mike
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!!!!
Boom!Cool Fun Class On Spill Results...Thank You Mike :)
You are welcome, Bike Brother darryl!!!!
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!!!!!
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 : )
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 : )
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")
Thanks Mike for this EXCELlent video.
You are welcome, Syed MM and Fellow Teacher : ) : ) : )
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 : )
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 ; )
Awesome Awesome Awesome as always Mike... thank you very much!
You are welcome very much, Edge!!!! : )
Old school is easier to understand than BYROW and LAMBDA.
That is true.
More reasons to get Microsoft 365. Awesome Mike, thanks!
You are awesomely welcome, Chris M!!!!
Cool, thanks Mike.
You are welcome, Luciano!!!!!
Awsome, Thanx Mike!
You are welcome!
Very good. Thanks Mike....
You are very welcome, Matt!!!!
Absolutely brilliant Mike. 👏 Thank u for your hard work u always bring the best 👌
Sir you are great teacher... Hats off
You are welcome, Farhan!!!!
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. RUclips comments have really been broken for about a year. I do not often get notifications... : (
@@excelisfun yeah nice one RUclips. 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
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
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 RUclips 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!!!!
Thanks!
AWS magic trick,
Go Team!!!!!!!
@@msantosh1220 Super Glad that you like the magic : ) : ) : ) : ) : ) : ) : )
Nice. Move on..... Sir
Glad you like it, Ubaidilah!!!
Excellent!!! Thak you and 👍:)
You are welcome, Most EXCELlent Teammate ; )
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!
Dear, I have bookings data recorded before and after the travel date. Hiw do I get the number of noshow passengers easily!?
Thanks
The link is ok Today 😃
: ) : ) : ) : ) : ) I thought of you when I was making the link. It is good to be on a good Team : )
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 : )
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
👍🏻
Glad you like it, Carlos!!!!
It would have been so much more intuitive if the programming allowed this ... BYROW(COUNTIFS(B7:C10,D4))
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}
Kindly make a sheet excel trile creteria match return value
I am sorry I do not understand what you are asking?