Count Below Hurdle: PivotTable, COUNTIFS, GROUPBY or Power Query? Magic Trick 1848
HTML-код
- Опубликовано: 15 июл 2024
- Download Excel File: excelisfun.net/files/EMT1847-...
Learn about how to count assembly line station times that are under 10 seconds using PivotTable, COUNTIFS function, GROUPBY function and Power Query.
Topics:
1. (00:00) Introduction
2. (00:06) Counting Assembly Line Post Times Less Than 10 Seconds
3. (00:43) COUNTIFS
4. (01:54) PivotTable with Helper Column
5. (02:57) GROUPBY Array Function
6. (04:05) Power Query
7. (05:22) Summary
8. (05:35) Closing, Video Links
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #countifs #groupby #pivottable #powerquery #powerquerytutorial
The only guy that deserves a like in youtube .
Thank you brother .
You are welcome, Brother Of Fun Excel!!!!!!
Thanks Mike. Groupby is amazing!!
Microsoft is so good to us : )
Thanks Amazing Mike for this EXCELlent video.
Glad you like it, Fellow Teacher!!!!
Thanks Mike, Nothing more efficient to add. GroupBy is my favourite.
Thanks, Teammate!!!!!!
Mike, Thank you for another magic boost!!
You are welcome!!!!
Amazing comparison ... thanks Mike
You are welcome, Hussein!!!!
Group By for this; great emt Mike. Thanks!
You are welcome : ) : )
daily dose of truth in data management
: ) : ) : )
Thank you for this magic trick and 👍!!! 🙂
Thank you Teammate Malina : ) : )
Thanks for another great video, Mike!
You are welcome, Roy!!!
@@excelisfun You are welcome, sir! I hope that your mom is getting better.
@@roywilson9580 , She passed away and is in a better place. I am still dealing with her estate and aftermath for a while to come.
@@excelisfun I am very sorry to hear that, Mike. My sincerest condolences on your sad loss.
We can all still COUNT on Mike to deliver!
Thanks for the COUNT : ) : )
Thank u Mike for this great Video 📹 👍
You are welcome, Nader : ) : )
Finally , a video to satisfactorily end my day
The all new excel function made it a lot interesting to move forward but you never fail to give a comparison
Thank you so much for the videos
You are so welcome for the videos!!!!
Hello Mike Hopefully you will be fine...God Bless you along life..
Thank you for the kind wishes!!!!
GROUPBY easy winner. However I always use PIVOTBY even when I could get by with GROUPBY. I started that practice after the second time I decided later to add columns to the mix!!
I love it: GROUPBY easy winner : ) : ) : )
Thanks MIke!!! I still don't have GROUPBY in my MS 365, and I'm on the Insider channel. Can't wait to get it
I can't wait for you to get it either. Come on Microsoft, speed it up!!!!!!!!!!!!!
I finally got it and PIVOTBY a few weeks ago. Which version are you on? I got it as of Version 2402.
Thank you for showing the several methods for counting. Countifs was easy enough I just wish the criteria syntax did not require the ""& at the = operators because that always gets me. =COUNTIFS(D3:D590,G10:G21,E3:E590,"
SUMIF, COUNTIF, COUNTIFS, SUMIFS and the like have been like that since the beginning. We just have to get used to it : )
Thanks mike..just correction the last EMT 1850
Yah... lol... I posted the EMTs out of order. I posted 1847, 1850, 1848 and next week will be 1849
Thanks : ) : )
i hope the groupby function get in excel 365 soon it is in insider to now
I hope so too : ) : )
Is there any way, power query load data from online drives "folder", without any sign-in. In onedrive, we need to give access to other people to the folder. Couldn't find google drive folder access in power query.
I have no idea. I don't think that is allowed for security.
@@excelisfun When we use folder in onedrive from browser link (till layout)..power query consider all files from all folders and creating query with base folders doesn't work for other people even if we give them access to specific folder we are using..do we have method to get data from onedrive folder and share so that other can use when we give them access to the onedrive folder.
I am looking forward to the release of groupby and pivotby
It will change a lot : ) Microsoft is very good to us!!
@@excelisfun I was trying to do a count with criteria inside a large Let formula, and Countif/s can't do it
I have learned Excel,PBI from your channels and now My level is upgraded...but I have Query maybe you can make a video on that...I have a PBI dashboard and I want to get some data from that visuals maybe in 4 excel files . and I want to put these 4 files in a folder then I have another files which is called a Merge files on which I have merge these 4 files data in 4 different sheet...I don't know what are the methods I can use to do this..maybe PowerAutomate , or directly from excel to import data from that PBI visual.
Hmm , any month now, but which year?
Hi Mike!
How are you?
Could I take you on your promise from some time ago?
Could I ask for a shout out in some future episode?
Also, it does not have to be a table - it can be named range as well :)
Yes, of course. I am still taking care of my Mom's estate after her passing. Until about April, 2024, I am making videos only one time a month. I have a video already recorded that will be out next week. So next time and make videos, I will give a shout out. You should send an email to me at excelisfun at gmail - to remind me that I should do this : )
Byrow and sumproduct works too.
=byrow(G10:G21,lambda(r,sumproduct(D3:D590=r,E3:E590
Wow!!! New School and Old School : ) : ) : )
1:12 How does that make things easier? AND defeates the ETA-LAMDA capabilities of BYROWS as well as GROUPBY??