Count Unique Days In Each Month: PivotTable, Array Formula, or Dynamic Array? EMT 1620
ฝัง
- เผยแพร่เมื่อ 13 ต.ค. 2024
- Download Excel File: excelisfun.net...
Learn all about how to count unique sales dates for each month. Make a Distinct Count calculations for number of unique dates in each month.
1. (00:01) Introduction
2. (00:54) Array Formula using FREQUENCY, SUMPRODUCT & IF Function
3. (04:22) Dynamic Array using FILTER and UNIQUE Functions
4. (05:48) Data Model PivotTable and Distinct Count Option
5. (08:58) Summary
Download Excel File: people.highline.edu/mgirvin/TH-camExcelIsFun/EMT1620.xlsx
Topics:
1. (00:01) Introduction
2. (00:54) Array Formula using FREQUENCY, SUMPRODUCT & IF Function
3. (04:22) Dynamic Array using FILTER and UNIQUE Functions
4. (05:48) Data Model PivotTable and Distinct Count Option
5. (08:58) Summary
@@realblacksmith You are welcome! Thanks for helping help with your comments and thumbs ups!
I SALUTE YOU, thanks t you im learning the non standard ways for using some functions! 6:00 like here with the rows, my mind is str8 forward, if anything needs counting i go str8 to count functions, you went for rows...
Thank you!
@@ExcelInstructor You are welcome for the ROWS fun : )
My vote...Power Pivot! Thanks for the video
Your vote is recorded!!!! I agree : ) : )
Data Model Pivot Tables definitely a major contribution!!
Yes, indeed, Data Model PivotTable to the rescue : )
Awesome Mike!
Thanks, teammate!
I loved that Pivot Table trick. Simply awesome 👍
Yes, sometimes PivotTables will be MUCH easier than formulas ; )
What a way to start my tuesday morning!! Simply Amazing!!! Thanks Mike. :) :)
You are welcome Formula Guy John Borg!!!
WOW! I didn't know about distinct count! Amazing! Thanks for sharing!
Yes, it is a cool PivotTable Tick : )
Thank Mike for this EXCELlent video. Superb.
Glad it is superb, Syed! Thanks for being such a consistent Teammate with your support : )
@@excelisfun you are welcome Mike. An honor to be in your team n at your support.
Thanks Mike...
Awesome Mike! That first formula had me breaking out the Excedrin 😀 !
Thanks Mike!
You are welcome, Dave!
Great solutions!
Thanks, Teammate!
Thank you for making my work easier!! You're the man, Mike!
Excellent as always....
EXCELlent, I think you meant, Syed ; )
So useful to know! Thanks Mike 😃
You are welcome, Roberto!!!
You are very talented. You are amazing in Excel, thank you for sharing.
You are welcome for thr Share, Hoi Yin Wan!
What a tutorial mike! God bless you. Thanks a lot.
You aer welcome a lot, Khan!!!
Thanks for this useful tip Mike
You are welcome for the tip, Kamran!!!
Thanks, I have addicted to your great tutorials even if I know some of them, I think this is the best way that you choose for every issue provide all of the way for all of version. Thumbs up. Keep following.
Data model is completely change the world of calculation and we need to concentrate on it more than always.
Glad you like the videos with multiple methods! If youwant to learn more about Excel Power Pivot. check out MSPTDA videos #13-15: th-cam.com/video/uEgFzdx-bhE/w-d-xo.html
That's a nice summary, Mike. I have long wondered why the DistinctCount operator is not part of native Pivot Tables.
The Distinct Count is a DAX Function that only lives in Power Pivot Data Model. I wonder too why there never added a Distinct Count, but that decision was made back in early 1990s, almost 30 years ago, when they invented the first PivotTable and SubTotal feature - when they only added 11 aggregate functions, and Distinct Count was not one of them.
another awesome presentation... thanks Mike
You are welcome, Edmundo!!!
Another great video from you, Mike. As I watch new videos as they are released, I may not have an immediate application from what I just watched/learned, but enjoy them nonetheless.
Cool, renegadek9! Even though the skill might not be used right now, it will be in the back of your mind later, and the videos are always here you you later! Thanks for your support, renegadek9!!!
Great comparison of different ways to get the same result. Power Query solution would be Date.StartOfMonth to get 1st day of each month, then remove duplicates from the original dates column and group by start of the month
Cool: Power Query Date.StartOfMonth solution! Thanks, Vida : )
Thanks for the amazing video, Mike! I just encountered this distinct count issue at work yesterday and used power pivot table to get it sorted out. I have never thought to use frequency function....always can learn something new from your video:):)
Yes, we have been using FREQUENCY as the essence for efficient Distinct Count formula for decades. I have a whole chapter just on that topic in my Ctrl + Shift + Enter book : )
Very useful thank you
You are welcome for the trio of usefulness, sadyaz64!!!!
Fantastic Mike! So grateful for your simple videos. In under 10 minutes, I saw the Dynamic Formula I was thinking of, but couldn't crack, and I saw the Power Query solution. I went with the Power Query solution this time!
Glad this helps!!!!
Counting distinct date using Rows function, that's very great, Thanks so much.
You are welcome so much, Ogwal!!
Great video Mike thanks for sharing!
You aer welcome for the share, Graham : )
Pure greatness. Thank You Mike :)
You are welcome, nimrodzik!!!
Thanks a ton Mike
You are welcome a ton, Khalid!!!
Hi Mike,
Amazing, always learn something new from you.
Thumbs up 👍
Thank you, Khalid!!!
Merci
You are welcome, Essaid : )
I tried summarizing the data with a pivot table before the video started to see if I could get it; but I couldn't because I didn't know about distinct count. Thank you for showing that.
Good for you for trying it first! You are welcome for the fun times with Excel, N Sanch01!!!
Glad to learn new excel stuff, thanks ExcelIsFun!
You are welcome, orlando!!!!
excellent
Thanks, MAJID!!!
I strongly believe that this exercise might short circuit my brain if I lose focus. On the other hand, it's a powerful mental workout, and I am thankful for it :-D Nice solutions, Mike!
Thank for your not short circuiting, Sandra : ) : )
Thank u so much Mr. Mike this is really fun and useful
Glad it is fun and useful, Ismail!!!
Amaizing Mike, came out with one cell formula (the only thing is we have to introduce 1/1/2019 in cell F42 ) =TRANSPOSE(MMULT(SEQUENCE(1,COUNTA(UNIQUE(Date)),1,0),(UNIQUE(Date)>=TRANSPOSE(F6:F41))*(UNIQUE(Date)
or without helper cell, using EOMONTH with INDEX ..
=TRANSPOSE(MMULT(SEQUENCE(1,COUNTA(UNIQUE(Date)),1,0),(UNIQUE(Date)>=TRANSPOSE(F6:F41))*(UNIQUE(Date)
Thanks for the alternative formula, cr gr0912!!!
great video mike! , my coworkers told me why we still use formulas the time we can do it with DAX
I don't know what to say ... the only response I have is the volume of data am I right?
There is not just one answer. You always have to look at 1) the data you have, 2) what the end result is, 3) then decide which tool to use. Generally speaking: Formulas: 1) might be good if you have small data, 2) There are Worksheet functions that you need that are easier than DAX, like Approximate Match Lookup (VLOOKUP or XLOOKUP or INDEX and MATCH) or Standard Deviation (STDEX.S or STDEV.P), 3) you need the solution to update instantly when source data changes. DAX and Data Model: 1) You have Big Data, anything over about 50,000 rows is a candidate for Data Model, 2) There are DAX functions that you need that are easier than Worksheet functions, like DISTINCTCOUNT or AVERAGEX (for daily, month or yearly averages) or even calculations for partial year sales, 3) you don't mind build a Data Model with Data Table and the like, 4) you don't mind refreshing when source data changes.
@@excelisfun clear! 🙂👍
Amazing!
Glad it is amazing for you, Jonathan!!!
Nice to learn how make distinct count with frequencyy thankssss
You are welcome, Mohamed!!!
Nice 💜
Glad it is nice for you, Lamees!!!!
Man... The complex array required in older versions of excel is enough to make even the most frugal among us to upgrade
That is well said. And yes, Office 365 has so many advantages, that all entities that want to be efficient with calculations and data analysis, will have to upgrade.
@@excelisfun But where is it? Our company has Office 365 but the new functions are not there.
@@sallyg5hlee636 You have to sign up for Insider: Excel File menu, Account, then Insider : )
Amazing stuff!... Thank you!
You are welcome, Arvind!!!
For me I concentrated more on the power table as its quick and can be manipulated later for other outcomes. But it was g8 to know about filter and unique functions. I have just brought office 365, am eager to use all ur magic trick in office 365.
Yes, PivotTables are usually the easiest! Glad you got Office 365, Santosh!!!
Nice comparison, especially for those who like the legacy formula challenge :) Not sure if this went over my head too fast, but when you put the two IFs consecutively (logical test and if true arguments) in the data array arguement of FREQUENCY, did that work like an AND logical test? If the lower and upper date limits were met then it was returning the dates that fell for that month?
Yes, two IF functions work as an AND Logical Test where it picks out dates between lower and upper limit.
@@excelisfun Cool. I never thought about constructing it like that. I usually put the AND function for something like that.
@@Sal_A We get to learn cool new things each day : ) : )
Dear Mike, I would like to know have you created any video on dashboards creation. It would be of g8 help to me and other teammate's.
I have made amny Dashboard videos. Here is a Playlist with Dashboard Videos: th-cam.com/play/PLrRPvpgDmw0kYt4c50Sg7BXGLBAjiW6VQ.html
OMG, I was thinking of just one but you have already created so many. Right now when I saw all the links I am supercharged to check all video in the link provided and get trained on Dashboard👍. A big thanks for reading and reverting on my request. Excel is fun with Mike girvin. ❤❤❤
@@msantosh1220 Remember, just as it is my duty to make the world a better place by providing free education to you and the rest of the world, it is your duty to click thumbs up and comment on each video that you learn from. So have fun with the videos and see you in the comments.
Yes Mike, I Will make sure that I like and leave at least three comment on each video that I watch. One again thankx for sharing your knowledge with all of the world ❤❤❤.
@@msantosh1220 With support like that from you and other viewers, we are a Team!!!! Go team : )
Please make tutorial video for material inventory software and hotel booking software
Hi Mike, in my Excel 2016 version, i need to use CTRL+SHIFT+ENTER to use the array function comparing 365 version you are using.
Yes, that is correct.
nice!
Hi Mike, I think I flunked this one,, I’m not sure what “unique days” means. I thought I would follow it anyway but I was thrown by the double use of Date in the frequency function. If you could help on these issues I would be very grateful. I’m using Excel 2016, but I’m on the Office 365 so Excel seems to be a hybrid of these two. Thanks.
I have a whole chapter in the book I wrote about this. I also posted the link at the end to the FREQUENCY video that explains it. Here is the link again: th-cam.com/video/uUrI8hoj8BA/w-d-xo.html
Thanks for the reply Mike. Which particular book that you wrote are you referring to? (There were a couple of different ones when I googled your name). Thanks.
@@davegoodo3603 Ctrl Shift Enter, Mastering Excel Array Formulas. But all the content in the book is free at TH-cam: th-cam.com/play/PLrRPvpgDmw0kjL4875H36yNhWBb0f-nci.html Videos 17 & 18 cover the FREQUENCy and Unique Count.
Thanks for that Mike, you da man!
Bro suggest me formula for
Like 2 month 4 days or 5 days(not on date)then auto calculate 2.5 month and if there is 3 month 24 days or 25 days then auto calculate 4 months.
Thanks
Hi Mike.. also.. note that your file link is: people.highline.edu/mgirvin/TH-camExcelIsFun/EMT16120.xlsx which lands at a Page Not Found error. I edited to /EMT1620.xlsx to get to the file :-)
Thanks for letting me know. I just fixed the link. It is working now.
Hi Mike.. great challenge. Just for fun, I came up with this array formula which produces the same result as the FREQUENCY formula construct:
{=SUM(IF(Date>=F6,IF(Date
Thoughts, I have a whole chapter just on comparing FREQUENCY and COUNTIFS for Unique Count formulas : ) There is no comparison. COUNTIFS can blow up your computer with slow calculations with only 10,000 rows. FREQUENCY is far superior to COUNTIFS for Unique Count. But, your formula is cool, especially since you invented it and had fun with this topic!!! Thanks for the formula, Wayne!
@@excelisfun Thanks Mike.. that was my intuition, as when copying the formula down I noticed a slight lag in the calculation of results. Thanks for the tip on FREQUENCY/COUNTIFS.. I'll go back and read that chapter in detail. I appreciate your guidance! :-)
@@wayneedmondson1065 Yes, when i wrote the chapter and did the tests, I was sort of stunned that COUNTIFS could be so slow.
In the pivot table how do you group the date into two columns, one for the year and the other for month? thank you
I showed in video : 1) Group, 2) then use Tabular Report Layout.
Design, Report Layout, Layout, Tabular.
Thank you so much. Yes, you have said it but I did not pay attention. Sorry.
@@adecarvalho Glad to help : )
6:36 Pivot Table Function
: )
@@excelisfun :)
Thanks Mike..
You are welcome, Khan Bhai!!!