SORTBY, UNIQUE, INDEX and SUMIFS Functions: Dynamic Spilled Array Reports. Excel Magic Trick 1671
HTML-код
- Опубликовано: 15 июл 2024
- Download Excel File: excelisfun.net/files/EMT1671....
Learn how to create a Dynamic Sales Report using the new Microsoft 365 Spilled Array Formulas.
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
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))))
LOVE The INDEX(F3#,,1)!!!! I have added it to the downloadable workbook. Go team!!!
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))
I aslo add the LET example. Awesome : )
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))))
@@excelisfun 😍 Thank you. I am so glad to be part of your team.
Good. Clean. Fun! I'm getting more out of your channel than the decades of whatever it was I was doing. Thank you!
It takes time to understand your lectures. The lectures are great and almost all inclusive. Thanks
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!!!!
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
So spilling of array only happens when we convert our data into table right?
Dude! You're a genius!
Just a guy having fun with Excel ; )
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!
Yes!!!!!
The fun is still going on with Excel. And you are a fun leader. :-)))
Thanks for all your work, Mike. Chapeau bas !!!
You are welcome, My Friend and Power Query Poet : )
You're a savior Mike!
Glad to help!
One year later, sitll find this trick useful. thanks!!!! I think a good use of unique/sort/xlookup and so can replace pivot table
Glad this helps!!!!
Brilliant, Mike. Many thanks.
Many You Are Welcomes, Ian!!!! : )
The unique function has a lot of uses indeed, I like the way you explain it, Thanks so much Mr Mike
Thanks Mike!! Brilliant as always!!
You are welcome, Sajid!!!
Thank you Mike, really helpful video 👍
Thanks, it was very helpful
Glad it helps!!!
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
Cameron from Busn 218 and Busn 216?
@@excelisfun Yes sir. That's my name don't wear it out
@@cameronsarrett1606 I will not wear it out!!! It is so great to see you hear at RUclips. Over the next 6 months I will be posting a new class here at RUclips 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!
Super tricks Mike. Thank you for sharing!
You are welcome, Ivan!!!
Thank you for these videos. I've been binging through your channel through this quarantine to speed up my excel skills.
Glad to help during lock down, Kai : )
Why would anyone give this video a “thumbs down?” Mike-excellent job as always; thank you!!!
Maybe some people confuse their rights and obligations. I thank you for your support, Cassius : )
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.
Just had a report to make the other day and it came out that way : )
Go Team!!!!
I see what you are saying, thank you. "DYNAMIC"
Amazing and superb!! Thank you mike.
You are welcome, Dhiman!!!
Boom!Spilled Arrays Rock..Great Tutorial Thank You Mike :)
That Boom is what makes the Spilled Arrays so much fun : )
Great fun with Dynamic Arrays
Great dynamic fun : )
Again a great video from you
Glad to help, as always : )
Thanks Mike. 👍
Brilliant. 🌟 👏 🌟
You are welcome, K D!!!!
Perfect ...thanks Mike
You are welcome, Hussein!!!!
Thanks for the share Mike ... This is an EXCELlent video.
You are welcome Syed MM : ) : : ) : )
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!!
Thanks, Wayne : )
Cool trick!
O, these dynamic arrays : )
Great as always...👌
Glad as alwasy that you like it, Sam Sami!!!
I like it, appreciate your efforts
You are welcome, Ammar!!!
Most excellent
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.
I hope you get it soon : )
Good like always!!!!!
Glad it is good for you, Joaquim : ) : )
I wait for F4 in tables too :). Thank you for this amazing exaple of spilled arrays!
You are welcome, teammate : )
Czesc Malinko 😀
Amazing!
Glad it is amazing for you, Khan!!!
Waoooo. Excellent video Sir.
Glad you Whaooo like it, Ashok : )
very useful video sir. i learnt a lot from it. thanks sir. please make a video on CONSOLIDATE function. thanks
Thanks Thanks
You are welcome!
Amazing Mike....Thanks. : ) : )
You are welcome, Formula Guy John : )
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.
Thanks for the 1671 edit. I have fixed it : )
I absolutely LOVE this: ,INDEX(F3#,,2) inside criteria 2 argument. Great alternative method!!!! I will add it to the workbook : )
@@excelisfun Thanks, these are some of things that you taught.
I really appreciate for your great trainings.
Nice technique!
Thumbs up!
Thanks Teammate : ) : )
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)
)
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...
Please what video did u use to learn table nomenclature?
Very nice ....
Glad it is nice for you, Amit : )
Amazing Thann you
Glad you like it, sadyaz64 : )
I don't know if I will ever use such a formula but nonetheless, you did a great job explaining!
Hi, Sir
Awesome and great video.
Glad it is great for you, A b h i s h e k !!!!
EXCEL-lent MIke, as always!
Thanks, as always, Teammate Chris : )
This was fun
We like fun : )
Good one
Glad it is good for you, Harish : )
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?!?! 🙂
Better late than never : )
wow it's cool
Glad it is cool for you, Mitun : ) : ) : ) : )
How do I give 100,000,00 of these: 👍 Thank you for always having the solutions I need years before I needed them!
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)
Well Done,
I usual use pivot table for this issue,
Yes, but I guess the beauty fo the formulas is instant update. But for Pivots it is just a simple refresh : )
@@excelisfun yes, indeed i missed this point, many thanks again
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.
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?
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.
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?
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 RUclips 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.
hi Mike, in your current example if we need to sort the data based on the sales how we can do that
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?
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...
Great Video Mike, could you please share me how to do running count withing Excel Table.
Thanks!
Formula like: =COUNTIFS(A$6:[@Name],[@Name])
Here is a video: ruclips.net/video/00A1sj8m4rI/видео.html
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?
Yes, you can turn off table formulas in Options. ANd yes, the ranges will expand : )
@@excelisfun Thamk you so much Mike :)
Does Unique function can get unique items from multiple worksheets.Kindly let me know.If so,then what is the syntax.
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.
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?
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: ruclips.net/video/l1-1aVgFth4/видео.html Then the very first class is the one you need. : ) : )
still using Excel 2019, missing out a lot... :(
What if the two data columns are not next to each other in your data table, what's the formula?
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)
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...
Very cool but just wondering what the advantages are to this technique vs using a pivot table to do the same thing?
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.
How can this be done with column & row addresses, & not tables?
Nice dynamic arrays
When it will be available for us mike have u any idea? We couldnt practise all this :-(
Microsoft says all of Office 365 in July.
Thank you for this video. How about using excel 2013, no UNIQUE AND SORTBY?
No. ONLY in Microsoft 365 Excel.
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
Is Unique function available in 2013 version
Only in Microsoft 365 Excel.
I miss expandable conditional formatting... :-)
: )
How can you make this in excel. We should make our life excel to be like you 💜
Purple Hearts!!!!!! Love them : ) : ) : )
@@excelisfun I love you 💜💜