Love your Great Video. 👍👍 we also can use array in Sort Index like this in F3 =SORT(UNIQUE(fSales[[Date]:[Product]]),{1,2}) and G3 =SUMIFS(fSales[Sales],fSales[Date],INDEX(F3#,,1),fSales[Product],INDEX(F3#,,2)) All in by Let =LET(u,SORT(UNIQUE(fSales[[Date]:[Product]]),{1,2}),CHOOSE({1,2,3},u,u,SUMIFS(fSales[Sales],fSales[Date],INDEX(u,,1),fSales[Product],INDEX(u,,2))))
@excelisfun
4 жыл бұрын
LOVE The INDEX(F3#,,1)!!!! I have added it to the downloadable workbook. Go team!!!
@excelisfun
4 жыл бұрын
I love it. I added this example to the download workbook: =UNIQUE(SORT(fSales3[[Date]:[Product]],{1,2})) =SUMIFS(fSales3[Sales],fSales3[Date],INDEX(J3#,,1),fSales3[Product],INDEX(J3#,,2))
@excelisfun
4 жыл бұрын
I aslo add the LET example. Awesome : )
@excelisfun
4 жыл бұрын
I was trying to roll it all up into LET, but could not figure it out, Excel Wizard. This formula is very great: =LET(u,SORT(UNIQUE(fSales3[[Date]:[Product]]),{1,2}),CHOOSE({1,2,3},u,u,SUMIFS(fSales3[Sales],fSales3[Date],INDEX(u,,1),fSales3[Product],INDEX(u,,2))))
@ExcelWizard
4 жыл бұрын
@@excelisfun 😍 Thank you. I am so glad to be part of your team.
@realtoast7036
2 жыл бұрын
Good. Clean. Fun! I'm getting more out of your channel than the decades of whatever it was I was doing. Thank you!
@zhiqizhang4348
Жыл бұрын
It takes time to understand your lectures. The lectures are great and almost all inclusive. Thanks
@excelisfun
Жыл бұрын
That is because you care and want to learn. I put it all here, but it is up to the viewer to work hard to learn and then prosper : ) Thanks for your kind words, zhiqizhang!!!!
@djkujo007
2 жыл бұрын
Dude! You're a genius!
@excelisfun
2 жыл бұрын
Just a guy having fun with Excel ; )
@BillSzysz1
4 жыл бұрын
The fun is still going on with Excel. And you are a fun leader. :-))) Thanks for all your work, Mike. Chapeau bas !!!
@excelisfun
4 жыл бұрын
You are welcome, My Friend and Power Query Poet : )
@cassiusclaudius3010
4 жыл бұрын
Mike-you seriously kick @$$! FINALLY I have access to Office365 and I am reviewing all your videos and updating my master “cheat sheet” before I start my new job. THANK YOU FOR ALL YOU DO!
@excelisfun
4 жыл бұрын
Yes!!!!!
@excelisfun
4 жыл бұрын
Topics: 1. (00:00) Introduction. 2. (00:29) Sorted Unique List of All Combinations of Dates and Products. 3. (00:45) UNIQUE Function. 4. (01:26) SORTBY Function. 5. (01:41) Creating Locked Table Formula Nomenclature. 6. (03:18) INDEX Function to create Two Separate Spilled Arrays. 7. (04:55) SUMIFS Function with Spilled Arrays. 8. (05:36) Conditional Formatting. 9. (06:48) End Video Links
@creativecookbook
4 жыл бұрын
So spilling of array only happens when we convert our data into table right?
@cassiusclaudius3010
4 жыл бұрын
Why would anyone give this video a “thumbs down?” Mike-excellent job as always; thank you!!!
@excelisfun
4 жыл бұрын
Maybe some people confuse their rights and obligations. I thank you for your support, Cassius : )
@slayermm1122
3 жыл бұрын
One year later, sitll find this trick useful. thanks!!!! I think a good use of unique/sort/xlookup and so can replace pivot table
@excelisfun
3 жыл бұрын
Glad this helps!!!!
@Excelambda
4 жыл бұрын
Amazing video, learned table nomenclature tricks from you, kudos for that.✌ Tried to get a single cell formula and different approach, joined the first 2 columns, applied unique then sort, mmult instead of sumifs, split results in 3 columns with choose. =LET(a,fSales[Date],b,fSales[Product],c,fSales[Sales], u,SORT(UNIQUE(a&b)), r,MMULT(--(u=TRANSPOSE(a&b)),c), CHOOSE(SEQUENCE(,3),--LEFT(u,5),RIGHT(u,LEN(u)-5),r) )
@excelisfun
4 жыл бұрын
Lovely!!!! Bill Szysz and I were working on a similar formula just a few days ago. TRANSPOSE and MMULT get around the array in array problem, but the formulas is so complicated...
@evelynnkpoku9127
4 жыл бұрын
Please what video did u use to learn table nomenclature?
@kaiyeung5330
4 жыл бұрын
Thank you for these videos. I've been binging through your channel through this quarantine to speed up my excel skills.
@excelisfun
4 жыл бұрын
Glad to help during lock down, Kai : )
@GeertDelmulle
4 жыл бұрын
WOW! Just, WOW! How on earth did you come up with that?! Just awesome: the first time I see the combo of table formula nomenclature and dynamic arrays. The result is wonderful.
@excelisfun
4 жыл бұрын
Just had a report to make the other day and it came out that way : ) Go Team!!!!
@ogwalfrancis
4 жыл бұрын
The unique function has a lot of uses indeed, I like the way you explain it, Thanks so much Mr Mike
@cameronsarrett1606
4 жыл бұрын
I don't take your class anymore, but I'll give you a like because this is the future of education. Keep up the good work
@excelisfun
4 жыл бұрын
Cameron from Busn 218 and Busn 216?
@cameronsarrett1606
4 жыл бұрын
@@excelisfun Yes sir. That's my name don't wear it out
@excelisfun
4 жыл бұрын
@@cameronsarrett1606 I will not wear it out!!! It is so great to see you hear at KZitem. Over the next 6 months I will be posting a new class here at KZitem that goes over Excel Basics and Advanced Excel, but with all the new methods in the newest Excel 365 version. The future just keeps on rolling out here at excelisfun. See you around, Super Smart Student Cameron!
@iankr
2 жыл бұрын
Brilliant, Mike. Many thanks.
@excelisfun
2 жыл бұрын
Many You Are Welcomes, Ian!!!! : )
@jacek.kalinski
Жыл бұрын
Thanks, it was very helpful
@excelisfun
Жыл бұрын
Glad it helps!!!
@zubairso
4 жыл бұрын
You're a savior Mike!
@excelisfun
4 жыл бұрын
Glad to help!
@sc1951
4 жыл бұрын
Hello Sir, I'm doing masters in statistics and I want to become a data analyst. To accomplish my goal I have to learn Excel from an expert. And finally I found out your KZitem channel... Here are a lot of videos and playlist... Now I'm not understand from where to start... Can you suggest to me a sequence of playlist from which I should start to learn... Right now I'm *Zero* in Excel... Thankyou so much to build this amazing Channel.
@anthonyverdin6743
3 жыл бұрын
I don't know if I will ever use such a formula but nonetheless, you did a great job explaining!
@Softwaretrain
4 жыл бұрын
Spill array is fun with Mike, Many thanks, My solution is this: use your formula without lock and wrap it up into Index for Data and Product as bellow: =UNIQUE(SORTBY(fSales3[[Date]:[Product]],fSales3[Date],,fSales3[Product],)) (1 for sorting is default) for Sales I used the following formula: =SUMIFS(fSales3[Sales],fSales3[Date],INDEX(F3#,,1),fSales3[Product],INDEX(F3#,,2)) I don't like lock address in table reference since it becomes long (if you use copy formula to the right instead of filling right, it doesn't need to be locked). Also, I think the title of this video should be EMT 1671 as you named your training file but it is 1670 right now.
@excelisfun
4 жыл бұрын
Thanks for the 1671 edit. I have fixed it : )
@excelisfun
4 жыл бұрын
I absolutely LOVE this: ,INDEX(F3#,,2) inside criteria 2 argument. Great alternative method!!!! I will add it to the workbook : )
@Softwaretrain
4 жыл бұрын
@@excelisfun Thanks, these are some of things that you taught. I really appreciate for your great trainings.
@jimfitch
4 жыл бұрын
Nice technique!
@sachinrv1
4 жыл бұрын
Very useful video Mike. Cheers :). I am still working in old Excel environment, but by the time I get Office 365, I am sure I will be fully equipped with all the functions, tricks and that amazing spilled array.
@excelisfun
4 жыл бұрын
I hope you get it soon : )
@wayneedmondson1065
4 жыл бұрын
Hi Mike.. another great lesson.. super tricks and tips. A great way to start the week :)) Thumbs up for Dynamic Spilled Array Reports and ExcelIsFun!!
@excelisfun
4 жыл бұрын
Thanks, Wayne : )
@paulsingleton6071
4 жыл бұрын
Thank you Mike, really helpful video 👍
@deepakmirchandani1348
4 жыл бұрын
very useful video sir. i learnt a lot from it. thanks sir. please make a video on CONSOLIDATE function. thanks
@jimfitch
4 жыл бұрын
Outstanding, Mike! As always. And very timely for me. Today I worked on an application that has several instances of SUMIFS or arithmetic formulas on spilled arrays, but had exactly the issue you address here. Glad to know this technique, but if I had done the right thing & watched this EMT when you released it, then I would have known it before I worked on today’s app. What was I thinking?!?! 🙂
@excelisfun
4 жыл бұрын
Better late than never : )
@sajidsherif5707
4 жыл бұрын
Thanks Mike!! Brilliant as always!!
@excelisfun
4 жыл бұрын
You are welcome, Sajid!!!
@MalinaC
4 жыл бұрын
I wait for F4 in tables too :). Thank you for this amazing exaple of spilled arrays!
@excelisfun
4 жыл бұрын
You are welcome, teammate : )
@not_to_blonde
4 жыл бұрын
Czesc Malinko 😀
@vida1719
4 жыл бұрын
Great fun with Dynamic Arrays
@excelisfun
4 жыл бұрын
Great dynamic fun : )
@sasavienne
4 жыл бұрын
Thanks Mike. 👍 Brilliant. 🌟 👏 🌟
@excelisfun
4 жыл бұрын
You are welcome, K D!!!!
@IvanCortinas_ES
4 жыл бұрын
Super tricks Mike. Thank you for sharing!
@excelisfun
4 жыл бұрын
You are welcome, Ivan!!!
@DougHExcel
4 жыл бұрын
Cool trick!
@excelisfun
4 жыл бұрын
O, these dynamic arrays : )
@SyedMuzammilMahasanShahi
4 жыл бұрын
Thanks for the share Mike ... This is an EXCELlent video.
@excelisfun
4 жыл бұрын
You are welcome Syed MM : ) : : ) : )
@samsami5923
4 жыл бұрын
Great as always...👌
@excelisfun
4 жыл бұрын
Glad as alwasy that you like it, Sam Sami!!!
@dhimangupta7800
4 жыл бұрын
Amazing and superb!! Thank you mike.
@excelisfun
4 жыл бұрын
You are welcome, Dhiman!!!
@als_bra5377
2 жыл бұрын
Really good material, thanks! Question: is it possible to get a spill array, starting from a 2D array, using sumifs to filter data by first columnand the by first row?
@aimeeparrilla4340
2 жыл бұрын
Hello Mike, Awesome video as usual, do you have a video for earlier versions prior to “365”, please help, stuck on older version with no other alternatives, thank you in advance Alex.
@ashoksahu9546
4 жыл бұрын
Waoooo. Excellent video Sir.
@excelisfun
4 жыл бұрын
Glad you Whaooo like it, Ashok : )
@amitghosh7275
4 жыл бұрын
Again a great video from you
@excelisfun
4 жыл бұрын
Glad to help, as always : )
@darrylmorgan
4 жыл бұрын
Boom!Spilled Arrays Rock..Great Tutorial Thank You Mike :)
@excelisfun
4 жыл бұрын
That Boom is what makes the Spilled Arrays so much fun : )
@Simoltz
4 жыл бұрын
Very cool but just wondering what the advantages are to this technique vs using a pivot table to do the same thing?
@excelisfun
4 жыл бұрын
Instant update. For about the 30 years that we have had PivotTables, that is the difference between summary reports with a Pivot or Formulas. The way you decide between PivotTables and Formulas for this type of report is whether or not you need instant update or you do not mind refreshing. It seems trivial, and so why not always use PivotTables all the time, but a significant amount of Excel Solutions require instant update. So we only go through extra work of formulas when we need instant update. Of course formulas can do infinitely more things that a PivotTable, but this comparison is for when the report can potentially be done both ways.
@sushantjoshi5865
4 жыл бұрын
Amazing tip this. Have one question on SORT formula though. Can we use dynamic array sort function and sort the data as per our custom list? I mean normal sort function has this feature. I am not sure whether dynamic array sort function also have it or not.
@amsarraj
4 жыл бұрын
I like it, appreciate your efforts
@excelisfun
4 жыл бұрын
You are welcome, Ammar!!!
@HusseinKorish
4 жыл бұрын
Perfect ...thanks Mike
@excelisfun
4 жыл бұрын
You are welcome, Hussein!!!!
@wadhaali4506
2 жыл бұрын
Thanks Thanks
@excelisfun
2 жыл бұрын
You are welcome!
@pmsocho
4 жыл бұрын
Thumbs up!
@excelisfun
4 жыл бұрын
Thanks Teammate : ) : )
@Sai-tek
4 жыл бұрын
Mike that's a great video. But I sometimes find following your instructions difficult. I think I should go through the fundamentals. Can you tell me which playlists to go through to understand and pace up my speed in learning the advanced concepts in excel?
@excelisfun
4 жыл бұрын
Yes, that is my specialty with over 3000 videos. I have classes and playlists for every level. My #1 best class for the fundamentals is : Excel Basics. But watch my 2 min video about how to find exactly what you need at the excelisfun cahnnel: kzitem.info/news/bejne/zWdjl5WMoHilm2k Then the very first class is the one you need. : ) : )
@ransuru
11 ай бұрын
Most excellent
@abhishek7308
4 жыл бұрын
Hi, Sir Awesome and great video.
@excelisfun
4 жыл бұрын
Glad it is great for you, A b h i s h e k !!!!
@sureshrouniyar3482
3 жыл бұрын
Does Unique function can get unique items from multiple worksheets.Kindly let me know.If so,then what is the syntax.
@isaiaguillon9878
4 жыл бұрын
Thanks Mike, Big fan!!! How about if you want to sort by the sumifs results, meaning the highest sale amount goes on top, is that possible?
@geeteshrathimeru9545
2 жыл бұрын
hi Mike, in your current example if we need to sort the data based on the sales how we can do that
@zhiqizhang4348
Жыл бұрын
I see what you are saying, thank you. "DYNAMIC"
@shakiraasfoor7599
4 жыл бұрын
Well Done, I usual use pivot table for this issue,
@excelisfun
4 жыл бұрын
Yes, but I guess the beauty fo the formulas is instant update. But for Pivots it is just a simple refresh : )
@shakiraasfoor7599
4 жыл бұрын
@@excelisfun yes, indeed i missed this point, many thanks again
@joaquimcosta952
4 жыл бұрын
Good like always!!!!!
@excelisfun
4 жыл бұрын
Glad it is good for you, Joaquim : ) : )
@mattschoular8844
4 жыл бұрын
I will need to watch that one again when my mind is fresh. Thanks for sharing those tips Mike. By the way, have I missed something, or is it not possible to use Xlookup/vlookup on a spilled array result?
@excelisfun
4 жыл бұрын
When I need to lookup a column, based on a Index Number, I think of INDEX. INDEX is a function specifically designed to lookup up whole columns or rows. I can't think how to use XLOOKUP to do that. But I am sure someone can...
@PHILIPROYBENEDICTLOUIS
4 жыл бұрын
Great Video Mike, could you please share me how to do running count withing Excel Table. Thanks!
@excelisfun
4 жыл бұрын
Formula like: =COUNTIFS(A$6:[@Name],[@Name]) Here is a video: kzitem.info/news/bejne/kWZ3l6egcZ9lpX4
@mohamedchakroun4973
4 жыл бұрын
Nice dynamic arrays When it will be available for us mike have u any idea? We couldnt practise all this :-(
@excelisfun
4 жыл бұрын
Microsoft says all of Office 365 in July.
@zhiqizhang4348
Жыл бұрын
I did UNIQUE first, then sort. When sorting, I built an array house {1,2} to specify sorting the first and the second column.SORT(UNIQUE(K24:L39),{1,2},1), then sumifs by SUMIFS(fSales[Sales],fSales[Date],M24:M31,fSales[Product],N24:N31)
@keithdutch5295
2 жыл бұрын
What if the two data columns are not next to each other in your data table, what's the formula?
@chrism9037
4 жыл бұрын
EXCEL-lent MIke, as always!
@excelisfun
4 жыл бұрын
Thanks, as always, Teammate Chris : )
@nadermounir8228
4 жыл бұрын
Amazing video as always Mike :) I am wondering if the excel table nomenclature is disabled so regular cell reference can used as well as F4 keys, but can the table expand in this case when new data in added?
@excelisfun
4 жыл бұрын
Yes, you can turn off table formulas in Options. ANd yes, the ranges will expand : )
@nadermounir8228
4 жыл бұрын
@@excelisfun Thamk you so much Mike :)
@rtrejos03
4 жыл бұрын
Hi. I'm trying to utilize the same logic but using countifs, but I'm getting an error. Any tips on what is causing the issue? Thanks in advance for your assistance.
@sadyaz64
4 жыл бұрын
Amazing Thann you
@excelisfun
4 жыл бұрын
Glad you like it, sadyaz64 : )
@TaxMentors
4 жыл бұрын
Very nice ....
@excelisfun
4 жыл бұрын
Glad it is nice for you, Amit : )
@khanbhai4349
4 жыл бұрын
Amazing!
@excelisfun
4 жыл бұрын
Glad it is amazing for you, Khan!!!
@hariishr
4 жыл бұрын
Good one
@excelisfun
4 жыл бұрын
Glad it is good for you, Harish : )
@johnborg5419
4 жыл бұрын
Amazing Mike....Thanks. : ) : )
@excelisfun
4 жыл бұрын
You are welcome, Formula Guy John : )
@paulaparecio971
4 жыл бұрын
Thank you for this video. How about using excel 2013, no UNIQUE AND SORTBY?
@excelisfun
4 жыл бұрын
No. ONLY in Microsoft 365 Excel.
@kamranb1369
4 жыл бұрын
This was fun
@excelisfun
4 жыл бұрын
We like fun : )
@ronverheijen5407
4 жыл бұрын
HI Mike - I saw you "estimated" the column length of the spilled array when you were doing conditional formatting. Do you know of a way/formula to automatically get the column length by month, say 31 days for May, 30 days for June, etc.(assuming that we craete these reports by calendar month)
@excelisfun
4 жыл бұрын
I am not sure how to do it because you have to highlight the range so that each cell gets a true false signal. I guess you might be able to use a dynamic range defined name... But even then, inside the dynamic range formula you would have to estimate...
@dougbitt
9 ай бұрын
How can this be done with column & row addresses, & not tables?
@CAKimberlyLewis
5 ай бұрын
How do I give 100,000,00 of these: 👍 Thank you for always having the solutions I need years before I needed them!
@dinethprabash1001
4 жыл бұрын
still using Excel 2019, missing out a lot... :(
@skillhub5073
4 жыл бұрын
wow it's cool
@excelisfun
4 жыл бұрын
Glad it is cool for you, Mitun : ) : ) : ) : )
@indersharma21
4 жыл бұрын
I need your assistance in implementing the trick on excel scenario. The below values in the cell on the row has the True and false. I want to figure out the count of repetition vs last change in the row. As the example below The Ture comes twice at first and then again it comes 4 times after changing from False. I want to compete for the count of repetition from the last change value in the cell on the row. Please advise. result count of a repetition TRUE TRUE 2 FALSE 1 TRUE TRUE TRUE TRUE 4 FALSE 1 TRUE 1 FALSE 1 TRUE TRUE 2 FALSE FALSE FALSE FALSE FALSE 5 TRUE 1
@FabioGambaro
4 жыл бұрын
I miss expandable conditional formatting... :-)
@excelisfun
4 жыл бұрын
: )
@Fida7648
4 жыл бұрын
Is Unique function available in 2013 version
@excelisfun
4 жыл бұрын
Only in Microsoft 365 Excel.
@lameessy2302
4 жыл бұрын
How can you make this in excel. We should make our life excel to be like you 💜
Пікірлер: 156