HomeОбразованиеRelated VideosMore From: ExcelIsFun

Traditional Arrays or Office 365 Dynamic Arrays? Count Unique Dates Product Was Sold. EMT 1534

208 ratings | 2990 views
Download Excel File: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1534-1535.xlsx Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm In this video learn two methods using Excel Spreadsheet Formulas to calculate a Unique Count of Dates Product Was Sold. Learn about the New Office 365 Dynamic Array Formulas and New Excel Calculation Engine or the Traditional Excel Array Formula Method. Learn about the new Office 365 FILTER Array Function, UNIQUE Array Function. See the standard Excel Functions: FREQUENCY, IF, SUM, ROWS and COUNT. Full Lesson on these new Dynamic Arrays and new Excel Calculation Engine: Comprehensive Excel Dynamic Array Formula Lesson: The Power of Array Formulas (EMT 1516) https://www.youtube.com/watch?v=7jJMDGZpjSk
Html code for embedding videos on your blog
Text Comments (65)
Mohamed Chakroun (8 days ago)
I will use dynamic arrays for big data only :-) Mike, because daynamic arrays make us lazy to use the traditional function. Thanks for frequency function refresh to find unique values. Thanks a lottttt
NoShadowOfDoubt (8 days ago)
="A unique Like!"
ExcelIsFun (8 days ago)
Thanks : )
Wayne Edmondson (11 days ago)
Hi Mike.. way cool use of DAF vs. traditional array formula. I especially like the use of ROWS() vs. COUNT() for the performance advantage on a large data set. Had not thought of that before watching your video. Thanks for another clever and unique tip and technique. Thumbs up!
ExcelIsFun (10 days ago)
I learned that tip (between ROWS and COUNT) in a Duel video with Mr Excel many years ago... : ) Thanks for having fun and watching, Wayne!!!!
Bart Titulaer (11 days ago)
Thank you Mike, this was a fair comarisson between the old and the new world with formulas. But "in the middle" there was/is an easier solution, not with formulas however: Just make a pivot and add the data tot the datamodel (checkmark), then put product on rows, and dates to values. It will count by default, but you can change this to ...."distinct count"....Although not formulas, it is faster to set up.... Greetings, Bart
ExcelIsFun (10 days ago)
Yes, great way to do it, Bart!!!! : )
QuickMadeUpName (11 days ago)
the new functions are neat and all, but i'm not seeing how they are more efficient and more user friendly than pivot tables or perhaps a power query option.
QuickMadeUpName (10 days ago)
+ExcelIsFun thanks for the reply Mike. Actually I've probably watched about 600 videos of your videos and have bought and read both of your books!! I'll try to see the benefit of these new functions. Thanks again for the reply.
ExcelIsFun (10 days ago)
It is simple. Since the PivotTable came out in mid 90s and then Power Query in 2013, you use formulas when you need the solution to update instantly. No other feature does this. For some business solutions this requirement is mandatory. For these situations, these new array formula are simply spectacular. BTW, QuickMadeUpName, I am not sure if you have seen all the class that I teach here at YouTube, but in all these classes I teach the fundamentals of formulas, PivotTables, Power Query, Power Pivot and Power BI in a way that makes these tradeoffs between the different awesome Excel tools we have clear. We are lucky that Microsoft provides all these tools, and each has its place when considering how to build a particular solution. ,
xBrianGG (11 days ago)
Great video. The Count Unique formula would be even better if there was a way to spill it rather than copying it down the rows.
ExcelIsFun (11 days ago)
I agree.
Sevag Barsoumian (12 days ago)
Thanks Mike One more time brilliant :)
ExcelIsFun (12 days ago)
You are welcome, Sevag!!!!
123IGRACH (13 days ago)
When I try "Before Dynamic Arrays" method, I'm getting "You can't use more that 8192 characters in an Microsoft Excel formula" error when I press F9 on first IF statement. You are not getting that error.
sadyaz64 (14 days ago)
As always great vidéo .thank you
Thanks for sharing this EXCELlent video Mike. This is amazing
Ross Walker (14 days ago)
Watching how easy this is with the new dynamic arrays is painful as I’m using CSE on a daily basis... come on Microsoft - let’s release this stuff already. Great video Mike.
RRR program (14 days ago)
Yet to get the new filter function in my office365
Excel Bear (14 days ago)
wow life is much easier~ the 1 dislike must really miss the array formulas
ExcelIsFun (14 days ago)
Thanks, Excel Bear!!!!!
Sanjeev Soni (14 days ago)
Brilliant Mike, you have made life easy for many excel users. Thanks a lot
ExcelIsFun (14 days ago)
You are welcome a lot! Thanks for the support, Sanjeev!!!
hashi856 (14 days ago)
Now if Microsoft you just release the new calc engine to the general public, I'd actually get to do this.
ExcelIsFun (14 days ago)
They say that ONLY Office 365 will have this. But it should be released in the next few months.
pmsocho (14 days ago)
FREQUENCY was/is cool but now so many of those crazy "work with uniques" formulas are much easier to write and understand for a regular Excel user :) Is it good or bad for us (folks who know Excel a little bit better than the average user)? :)
pmsocho (14 days ago)
+ExcelIsFun :)
ExcelIsFun (14 days ago)
It is good. It is especially good since most of us will work for many years into the future, and although currently few people have the new functions, in a few years everyone will be on Office 365, and so we will all have the same version and can use these new and easier formulas and share freely : ) Thanks for your comment, Teammate!!!!
Vida (14 days ago)
Great to know both methods
ExcelIsFun (14 days ago)
It is nice to know both : ) Thanks for the support, Vida!!!
David Berger (14 days ago)
Great! Thank you for your content and passion!
ExcelIsFun (14 days ago)
You are welcome, David!!!! Thanks for the support : )
Leila Gharani (14 days ago)
Love dynamic arrays. So easy to use! Thank you Mike for another great tutorial.
ExcelIsFun (14 days ago)
You are welcome Teammate : ) : )
ExcelStrategy (14 days ago)
The traditional CSE formula is rock solid and it will work in any Excel version this wins for me. Let me add that the construct with the new calc engine is not so easy at all.
ExcelIsFun (14 days ago)
Thanks for the feedback, ExcelStategy : )
Kevin Carter (14 days ago)
I'm really glad I worked on learning the old way with CSE from the Professor and the book. It makes me really appreciate the genius involved in both the old and new!!!
ExcelIsFun (14 days ago)
I agree: it is fun to know the old and new : ) But here is the funny thing: we are only a few months into the new arrays, and I already dream up my formulas with the new methods, not the old... Thanks for your support, Kevin : )
Geert Delmulle (14 days ago)
The first one was very nice, but the DAF variant is way more elegant IMO. I’m still counting the days until my excel at work has DAF (no pun intended).
ExcelIsFun (14 days ago)
+Geert Delmulle , Can't wait for the next one : )
Geert Delmulle (14 days ago)
You’re welcome. And you deserve every bit of all of our support. See you on the next one.
ExcelIsFun (14 days ago)
Love the pun!!!! Me too - because I can't wait to start teaching these in my Highline Classroom classes also : ) Thanks for your amazing support, Geert : )
Finance in 5 Minutes (14 days ago)
After watching this video I feel I am thousands miles away from this knowledge .I have to learn a lot of things .Great video
Finance in 5 Minutes (14 days ago)
ExcelIsFun Yes
ExcelIsFun (14 days ago)
+Finance in 5 Minutes Yes, Phone Excel can't do most of What a Computer Excel can do. But, it can make finance calculations well, as you show : )
Finance in 5 Minutes (14 days ago)
ExcelIsFun oh Great but phone Excel have Limitations that Even You use External keyboard ctrl + Shift + Enter doesn't work ( or maybe I don't know how to use it I tried but I didn't get much success.) You Don't have pivot table & Many stuff but if You have Formula ( Guys like You who knows all the Formulas & keyboard shortcuts) You can do a lot of things .Here I am trying to explore as Much as I can do with this phone Excel.
ExcelIsFun (14 days ago)
+Finance in 5 Minutes I have tried to post lots of stuff for the last decade : ) But since I have never posted videos about Excel on the Phone, that is where a Teammate like you can help the awesome Online Excel Team!!! : )
Finance in 5 Minutes (14 days ago)
ExcelIsFun You Are an Excel university
Excel On Fire (14 days ago)
Nice!
ExcelIsFun (14 days ago)
Nice comment, Oz : ) : )
John Borg (14 days ago)
Thanks Mike. That was great fun watching. :) :)
ExcelIsFun (14 days ago)
Fun to watch, fun to make -- it is a win win : ) Thanks for your support, John!
Ismail Ismaili (14 days ago)
thank you so much Mr. Mike that was great to know I really appreciate
ExcelIsFun (14 days ago)
You are welcome, Ismail!!! Thanks for your consistent support!!!
Hussein Korish (14 days ago)
Amazing .... Thanks Mike
ExcelIsFun (14 days ago)
Glad it is amazing, Hussein!!! Thanks for your support : )
Chris Mancinelli (14 days ago)
The "Before" way is painful Mike! I can't wait until my Office 365 gets the new Filter function! Great video
ExcelIsFun (14 days ago)
Yes, but we have been using that painful way for decades... But it is now a new and easier frontier!!!! Thanks for your support and comments, Chris !!!!
Richard Hay (14 days ago)
Great way to show the advantages of using the new DAF. In your competition the winner is clear!!
ExcelIsFun (14 days ago)
Yes, there is definitely an advantage to the DAF : ) Thanks for the comment and support, Richard : )
Beepeta Rajani Kanth (14 days ago)
Please do videos in ms access also
ExcelIsFun (14 days ago)
I am sorry, but I am not an expert in Access, so I can not make videos beyond the very basic videos that I have posted. Sorry about this.
Sandra Vukovic (14 days ago)
Cool! I haven't used the frequency formula that much, but somehow the logic of Office 365 version seems to be more, hmm, logical? :-) Anyway, thank you for both solutions! Will be trying them out "in the real world" soon.
ExcelIsFun (14 days ago)
Yes, the newer functions seem to be much more conceptually easy to understand. Thanks for your comment and support, Sandra!!!!

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.