Thank you soooooooooooooo much for this. I am a new excel user who started working for an accounts job, I have been searching for this function and had a struggle. this solved my problem and saved a lot of time. Thank you once again.
Always going to leave a comment and a thanks to boost that engagement score on youtube. Thank you for these videos, I'm constantly learning something new.
Great example. I really like the new dynamic array calculation engine, it really simplifies things, moreover, if you combine it with LET(...) and LAMBDA(...); but (there is always a 'but'), Excel should find the way to deal with conditional formatting for dynamic arrays; i.e. the "Applies to" actually accepts # operator, but immediately transforms it into an absolute range instead of keeping it dynamic to the extension of the array given new data entries. So far we can do complex dynamic formulas which update/expand with new data, but the formats do not expand as well. Thank you for your amazing labor.
I agree about they should add an argument to add formatting. But so far I do not mind, because we always had to manually add formatting anyway : ) You are welcome for the labor, J. Obando!!!!
@@GeertDelmulle Can't do anything now, cux I hard coded it into video : ) As you know I am a bad speller and I am dyslexic and can't see something that is misspelled in front of me : )
@@excelisfun Mike, I understand. Maybe, because of the increased probability of making spelling errors, you can amend your workflow as follows: every piece of text you want to put in a video, you gather in a Word document first, there you can use the spelling checker. It’s not that much extra work, I think (you can copy-paste when going from Word to C****a). Just think of that notorious poem: I’ve got a spelling checker, It came with my pea sea. It plane lee marques for my revue, Miss steaks I can knot sea. ... :-)
I do that already : ) : ) : ) Word gives me the red squiggles. However, I am also forgetful and so sometimes I forget. Not knowing how to type and spell is one of my worst curses. But, you do not know and no one really here knows, but I grew up in Oakland CA and went to public schools and then a hippy school in Berkeley for High School, and I graduate High School illiterate. I did know basic math or how to read and write. It was only crazy hard work in community colleges in the 80s and then later in the 90s that got me some basic skills. I still am plagued by this lack of early education.
Great video!!! Maxifs rules!!!! Best and only formula for single cell!!!! , Aggregate, powerful!! Some variations, (not for single cell): =MAX(($B$7:$B$29=L7)*$D$7:$D$29) =MAX(FILTER($D$7:$D$29,$B$7:$B$29=L7)) And just for fun, a single cell that creates a header also Inv #, Last amount, Last date , Total amounts =LET(i,B7:B29,a,C7:C29,d,D7:D29,ui,SORT(UNIQUE(i)),ld,MAXIFS(d,i,ui),la,SUMIFS(a,d,ld,i,ui),ta,SUMIFS(a,i,ui),rs,CHOOSE({1,2,3,4},ui,la,ld,ta),tr,{"Inv #","Last amount","Last date","Total amounts"},r,ROWS(ui)+1,sr,SEQUENCE(r )-1,rsf,SWITCH(sr,0,tr,INDEX(rs,sr,SEQUENCE(,4))),rsf) Sorry that I posted so late, had to do some xmas prep with some friends!!😊😊
Yes, and the FILTER is THE best to do any complicated Logical Test for aggregate calculations, like OR Logical Test on Multiple Columns : ) Thanks for your set of amazing formulas. P.S. I got the money bag and bike and computer presents under the tree : )
Yes, there are so many ways to do this, but fundamentally it is a max calculation, and MAXIFS seems to win : ) It is so good it is sort of like cheating lol
Thank you soooooooooooooo much for this. I am a new excel user who started working for an accounts job, I have been searching for this function and had a struggle. this solved my problem and saved a lot of time. Thank you once again.
You are welcome!
All the Excel beauty in one video only. Thank you Mike!
You are welcome for the Excel Beauty and Fun, Teammate Celia : ) : ) : ) : )
Your videos are ALWAYS GOLDEN NUGGETS
Glad you like the golden nuggets : ) : ) : )
Fantastic Mike ! There is more to "Excel" than meets the eye. Cheers !
I LOVE that: "There is more to "Excel" than meets the eye"!!!!!
Got the answer today why large is used instead of max in aggregate function. Something to learn everytime I watch your videos. Thanks.
Yes!!!!! I love to hear that you learned that odd fact about AGGREGATE : )
Excel is treasure u r revealing it using skill........... appreciate a lot Mike
Thank you Mike. Merry Christmas!
You are welcome, Luciano !! Merry Christmas to you too : )
Wow! You're a true Excel Guru. Thanks for sharing!
Excellent as usual.
Glad it was EXCELlent for you, Mark!
As Usually you make it amazing
Glad it is amazing for you, Ashraf!!!!
Always going to leave a comment and a thanks to boost that engagement score on youtube. Thank you for these videos, I'm constantly learning something new.
Yes! We are all a Team in that way: I post fun stuff, you and other Teammates engage and the party keeps continuing : )
Excel 365 makes life so easier.
Yes it does : )
Wow. That's what we've been looking for. Thanks Mike for this EXCELlent video.
Cool, were you looking for "lookup late date"? Or were you looking for all the different options to a given problem?
@@excelisfun I usually look for all the different options and approaches to a given problem. Whatever suits better, I go for that.
@@SyedMuzammilMahasanShahi That is smart : )
So many options; thank you for always showing more ways to get things done.
You are welcome, Dennis D!!!!!
Thanks Mike. Love them all!!!
You are welcome, Formula Guy John : ) : ) : )
Great as always Mike, thanks !
You are welcome as always, Chris M : ) : ) : ) : )
Boom!Dynamic Array Formulas Never Fail To Put A Smile On Your Face!!!Great Tips...Thank You Mike And Happy Holidays To You And Your Family :)
Happy Excel Trick Happy Face Holidays to you and your family, darryl : ) : ) : ) : )
MAXIFs ♥. Thanks for fantastic video!
You are welcome, Teammate : )
Perfect ... Thanks Mike
You are welcome, Hussein!!!!
Thanks for the various ways to do date lookups!
You are welcome, Teammate! Happy Holidays, Doug : )
Thanks Mike, another awesome video.
You are welcome, Iwan : ) : ) : )
Great example. I really like the new dynamic array calculation engine, it really simplifies things, moreover, if you combine it with LET(...) and LAMBDA(...); but (there is always a 'but'), Excel should find the way to deal with conditional formatting for dynamic arrays; i.e. the "Applies to" actually accepts # operator, but immediately transforms it into an absolute range instead of keeping it dynamic to the extension of the array given new data entries. So far we can do complex dynamic formulas which update/expand with new data, but the formats do not expand as well. Thank you for your amazing labor.
I agree about they should add an argument to add formatting. But so far I do not mind, because we always had to manually add formatting anyway : )
You are welcome for the labor, J. Obando!!!!
You are knowledge bomb!.
Always gr8 tips and tricks
Love to help you with the knowledge bombs : ) : ) : ) : ) : ) : )
Great comparison - those dynamic arrays formulas are addictive for their real-time behavior. Love ‘m!
Mike, check your spelling in the video call-outs (...ment, not mnet), twice.
@@GeertDelmulle Can't do anything now, cux I hard coded it into video : )
As you know I am a bad speller and I am dyslexic and can't see something that is misspelled in front of me : )
Love the real time spill : )
@@excelisfun Mike, I understand. Maybe, because of the increased probability of making spelling errors, you can amend your workflow as follows: every piece of text you want to put in a video, you gather in a Word document first, there you can use the spelling checker. It’s not that much extra work, I think (you can copy-paste when going from Word to C****a).
Just think of that notorious poem:
I’ve got a spelling checker,
It came with my pea sea.
It plane lee marques for my revue,
Miss steaks I can knot sea.
... :-)
I do that already : ) : ) : ) Word gives me the red squiggles. However, I am also forgetful and so sometimes I forget. Not knowing how to type and spell is one of my worst curses. But, you do not know and no one really here knows, but I grew up in Oakland CA and went to public schools and then a hippy school in Berkeley for High School, and I graduate High School illiterate. I did know basic math or how to read and write. It was only crazy hard work in community colleges in the 80s and then later in the 90s that got me some basic skills. I still am plagued by this lack of early education.
Interesting approaches, thanks for sharing Mike, all the best in 2021
You are welcome for the share, janez!!!
Thank Mike 👍🏻
You are welcome, Sevag!!!!
Thank you so mutch, Sir.
You are welcome so much, Sir!!!!!
Excellent Mike, as usual if I can say ;-) Merry Xmas and take care of you. Cheers from the other side of the pond. !
Cheers and Merry Christmas to you too, Pierre!!!!
Another great tutorial. I’ve been missing your videos
Glad you are back to watch and have fun with more videos, Patrick!!!!
Amazing as usual! Thank you so much!
You are welcome so much, caraquegno!!!!!
Thanks, I really enjoy your videos👍Merry Christmas Mike🎄🎄🎄
You are welcome for the videos, Davor!!! Merry Christmas to you too : ) : ) : )
Wonderful helpful video!!
All for long time excelisfun viewer: YOU, Darlene "Peachy Motor Cyclist" Pirozzi!!!!!!!
Mike, u r my hero
Always glad to help : )
Mike, thank you for another great year, and have a Merry (and safe - I can't avoid saying that quite yet) Christmas.
Merry Christmas to you too, Henry : ) : )
Dynamic Arrays formulas are my preference in most cases
Me too : ) Spilling stuff is fun ; )
you are really great 👍🏻👍🏻👍🏻👍🏻💯💯💯
Thank you, Vijay : )
Thanks, it's really useful.
You are welcome, Software Train : )
Thanks Mike 👌
You are welcome, Yaser!!!!
Merry Chrisrmas to you Sir Mike and your family & friends!!!🇱🇷
♥️💚🧡🎁🎆🎄🎀💝🌐🎁🎆❄☃️✨🎁🎄💗💖💙
From the 🇵🇭
Merry X-mas and a happy New Year to you too!!!!! Thanks for the Christmas like heart x-mas presents all year around, Delight In Life : ) : ) : ) : )
Array formulas are even more fun when you see your collegues' reaction each time they update in real time 😂😂😂
Thanks Mike!
(I mean, when the formulas update, not the colleagues 🙃 )
Actaully, don't they both update? Formula with new data and colleagues with the new knowledge of Spilled Arrays Abilities ; )
@@excelisfun haha you're right :)
Additional way is lookup function =LOOKUP(2;1/(lookup_range=criteria_cell);result_range)
Yes!!! That is an awesome formula !!!!!! Thanks, Sameh : )
@@excelisfun U R the boss, we always learning from you
@@sameh1180 I am glad to help our Team : )
I think it also works using multiplication beside division
Yes it will work for max dates. It works when the 0 will not create an incorrect result.
I have 2013 excel still I will use pivot. Pivot is My favorite
It is THE fastest and easiest : ) : ) : ) : )
I have missed your Voice so much Thanks for all the support
You are welcome for all the support, arteogr!!!!
👍👍
Great video!!! Maxifs rules!!!! Best and only formula for single cell!!!! , Aggregate, powerful!! Some variations, (not for single cell):
=MAX(($B$7:$B$29=L7)*$D$7:$D$29)
=MAX(FILTER($D$7:$D$29,$B$7:$B$29=L7))
And just for fun, a single cell that creates a header also Inv #, Last amount, Last date , Total amounts
=LET(i,B7:B29,a,C7:C29,d,D7:D29,ui,SORT(UNIQUE(i)),ld,MAXIFS(d,i,ui),la,SUMIFS(a,d,ld,i,ui),ta,SUMIFS(a,i,ui),rs,CHOOSE({1,2,3,4},ui,la,ld,ta),tr,{"Inv #","Last amount","Last date","Total amounts"},r,ROWS(ui)+1,sr,SEQUENCE(r )-1,rsf,SWITCH(sr,0,tr,INDEX(rs,sr,SEQUENCE(,4))),rsf)
Sorry that I posted so late, had to do some xmas prep with some friends!!😊😊
Yes, and the FILTER is THE best to do any complicated Logical Test for aggregate calculations, like OR Logical Test on Multiple Columns : )
Thanks for your set of amazing formulas.
P.S. I got the money bag and bike and computer presents under the tree : )
before the last payment amount and date show in excel 2016
I tried XLOOKUP with reverse search (last to first) but MAXIFS is very quick and fantastic.
Yes, there are so many ways to do this, but fundamentally it is a max calculation, and MAXIFS seems to win : ) It is so good it is sort of like cheating lol