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

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

226 ratings | 3617 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 (67)
edgie da pogi (23 days ago)
I love learning excel with your method Mike... Thanks again :)
ExcelIsFun (23 days ago)
You are welcome, edgie!!!! Thanks for your amazing support : )
Mohamed Chakroun (2 months 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 (2 months ago)
="A unique Like!"
ExcelIsFun (2 months ago)
Thanks : )
Wayne Edmondson (2 months 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 (2 months 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 (2 months 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 (2 months ago)
Yes, great way to do it, Bart!!!! : )
QuickMadeUpName (2 months 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 (2 months 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 (2 months 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 (2 months 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 (2 months ago)
I agree.
Sevag Barsoumian (2 months ago)
Thanks Mike One more time brilliant :)
ExcelIsFun (2 months ago)
You are welcome, Sevag!!!!
123IGRACH (2 months 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 (2 months ago)
As always great vidéo .thank you
Thanks for sharing this EXCELlent video Mike. This is amazing
Ross Walker (2 months 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 (2 months ago)
Yet to get the new filter function in my office365
Excel Bear (2 months ago)
wow life is much easier~ the 1 dislike must really miss the array formulas
ExcelIsFun (2 months ago)
Thanks, Excel Bear!!!!!
Sanjeev Soni (2 months ago)
Brilliant Mike, you have made life easy for many excel users. Thanks a lot
ExcelIsFun (2 months ago)
You are welcome a lot! Thanks for the support, Sanjeev!!!
hashi856 (2 months ago)
Now if Microsoft you just release the new calc engine to the general public, I'd actually get to do this.
ExcelIsFun (2 months ago)
They say that ONLY Office 365 will have this. But it should be released in the next few months.
pmsocho (2 months 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 (2 months ago)
+ExcelIsFun :)
ExcelIsFun (2 months 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 (2 months ago)
Great to know both methods
ExcelIsFun (2 months ago)
It is nice to know both : ) Thanks for the support, Vida!!!
David Berger (2 months ago)
Great! Thank you for your content and passion!
ExcelIsFun (2 months ago)
You are welcome, David!!!! Thanks for the support : )
Leila Gharani (2 months ago)
Love dynamic arrays. So easy to use! Thank you Mike for another great tutorial.
ExcelIsFun (2 months ago)
You are welcome Teammate : ) : )
ExcelStrategy (2 months 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 (2 months ago)
Thanks for the feedback, ExcelStategy : )
Kevin Carter (2 months 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 (2 months 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 (2 months 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 (2 months ago)
+Geert Delmulle , Can't wait for the next one : )
Geert Delmulle (2 months ago)
You’re welcome. And you deserve every bit of all of our support. See you on the next one.
ExcelIsFun (2 months 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 (2 months 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 (2 months ago)
ExcelIsFun Yes
ExcelIsFun (2 months 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 (2 months 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 (2 months 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 (2 months ago)
ExcelIsFun You Are an Excel university
Excel On Fire (2 months ago)
ExcelIsFun (2 months ago)
Nice comment, Oz : ) : )
John Borg (2 months ago)
Thanks Mike. That was great fun watching. :) :)
ExcelIsFun (2 months ago)
Fun to watch, fun to make -- it is a win win : ) Thanks for your support, John!
Ismail Ismaili (2 months ago)
thank you so much Mr. Mike that was great to know I really appreciate
ExcelIsFun (2 months ago)
You are welcome, Ismail!!! Thanks for your consistent support!!!
Hussein Korish (2 months ago)
Amazing .... Thanks Mike
ExcelIsFun (2 months ago)
Glad it is amazing, Hussein!!! Thanks for your support : )
Chris Mancinelli (2 months ago)
The "Before" way is painful Mike! I can't wait until my Office 365 gets the new Filter function! Great video
ExcelIsFun (2 months 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 (2 months ago)
Great way to show the advantages of using the new DAF. In your competition the winner is clear!!
ExcelIsFun (2 months ago)
Yes, there is definitely an advantage to the DAF : ) Thanks for the comment and support, Richard : )
Beepeta Rajani Kanth (2 months ago)
Please do videos in ms access also
ExcelIsFun (2 months 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 (2 months 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 (2 months 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.