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

Count Number Products Battle: Excel? DAX? Power Query? Office 365? Excel Magic Trick 1533

352 ratings | 6700 views
Download Excel Start Files: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1533Start.xlsx Download Excel Finished Files: https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT1533Finished.xlsx In this video compare five methods to count how many products are in a column. Topic: (00:05) Introduction 1. (01:25) Excel Spreadsheet Function: COUNTIFS 2. (03:23) Excel Standard PivotTable 3. (04:47) Power Query: Group By feature 4. (07:23) DAX Functional Language of Excel Power Pivot & Power BI DAX: COUNTROWS 5. (11:08) Office 365 Dynamic Array Functions: UNIQUE & COUNTIFS (14:40) Summary Entire page with all Excel Files for All Videos: http://people.highline.edu/mgirvin/excelisfun.htm 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)
Html code for embedding videos on your blog
Text Comments (85)
Steven Nye (1 month ago)
excellent information
ExcelIsFun (1 month ago)
EXCELlent!!!! : )
pmsocho (3 months ago)
Thanks for the video! :)
Atiqa Raj (3 months ago)
I forgot to mention in power query
Atiqa Raj (3 months ago)
Hi I have data repeated in columns like below Items Price Region Items Price Region Then in the second column I have the data How I can transpose the content or the row to be the column header and get a summary table
Luciano (3 months ago)
You're brilliant Mike! ..... This is the magic of Excel, the same result in different ways. Thank you and Happy New Year.
Wayne Edmondson (3 months ago)
Hi Mike.. EXCEL POWER.. Love it! Thanks for the 5 methods to count.. especially the option using SORT, UNIQUE and COUNTIFS with #.. so cool! Thumbs up!
Vida (3 months ago)
Happy New Year Mike! Good comparison of different methods. Dynamic arrays was the most efficient one - easy to create formulas and automatic update. If Microsoft did automatic refresh for Pivot tables and Power Query after changing/adding data to the data table, these methods would be efficient too.
Ismail Ismaili (3 months ago)
thank you so much for the video it's really fascinating
Armaan kadiwala (3 months ago)
Hi sir i want your favour I have a data like in Column A first cell there is a data after that in second cell I have text like four 5 names again I have a date and text but DATA in the same column so how do I separate the data and text....pls help me
Kevin Lehrbass (3 months ago)
The # technique! Wow! Thanks for the amazing video Mike!
ExcelIsFun (3 months ago)
You are welcome, Teammate Kevin : )
Priya S (3 months ago)
Hi. I just discovered this channel. Im Bachelor of commerce student, mba(finance) aspirant. Can you help me navigate through all the videos that'll be relevant for me? There are like a bajillion of them here and I'm just confused. Thanks in advance.
M. SZ. (3 months ago)
Excellent topic. We all need to count things on data sheets. Thank you.
Caronte (3 months ago)
Malina C. (3 months ago)
Thank you Mike for this video, and all you've posted this year. I always learn a lot from your videos and have a great fun :). Can't wait "early 2019" to practise along with you :)
ExcelIsFun (3 months ago)
You are welcome! It has been a fun year and next year should be even more fun : )
oktibri pribadi (3 months ago)
Hi, is there anyone who could help me with too long countif formula?. Let's say i have 84 criterion that i want to lookup on my data list. after i typed it excel returns with dialog box says you can't use more than 8192 characters in a ms. excel formula.
ExcelIsFun (3 months ago)
For back and forth dialog to get Excel solutions try this great Excel Question Forum: mrexcel.com/forum
fabry Espejo (3 months ago)
Muchas gracias y Feliz Navidad!!
ExcelIsFun (3 months ago)
Thanks, fabry!!!!! Happy Holidays : )
edgie da pogi (3 months ago)
Another Amazing Video Mike...Can't wait to have the updated office 365... Thank you very much Mike!
ExcelIsFun (3 months ago)
You are welcome so much, edgie!!!!! Thanks for your support : )
ARUN ARK (3 months ago)
💐💐💐👍👍👍👍👍👍👍 very informative... Thank you 💐💐💐💐👍👍👍👍
ExcelIsFun (3 months ago)
You are welcome!!!!! Thanks for the Thumbs Ups and Flowers!!!!!
Black Star (3 months ago)
Great video!, I was wondering. I have a two lists of data that have different dates that I need to pair up in the same row. One dataset contains weekends and the other does not so they are hard to match up.. Any tips? Looks like this but I guess I need gaps between the days where the dates don't match 12/12/18 . 11/12/18 10/12/18 10/12/18 8/12/18 9/12/18
ExcelIsFun (3 months ago)
I do not have a good solution for you, sorry. For back and forth dialog to get Excel solutions, try this great site: mrexcel.com/forum
Esther k! (3 months ago)
Nothing short of "Epic"!
ExcelIsFun (3 months ago)
Glad it is Epic for you, Esther k! !!!!!! Thanks for your support : )
Bart Titulaer (3 months ago)
Thank you Mike, I like your comparison of the different methods. I think your solution was 98% complete. Where is the other 2%? Well for getting the names, you could also use filter special (or advanced?) and select "only duplicates" . Although this saves a few clicks I seldom use this method...The other is also a very rare solution: If you beam the data to the data model, you can convert with the OLAP tool tool formula (KUBEVALUE). But although it is a formula, you still need to refresh to update with new data. Anyway: I wish you all the best for 2019. Thanks, many thanks to all your videos in 2018 and your enthusiastic way of presenting. Greetings, Bart
ExcelIsFun (3 months ago)
Thanks, Bart!!!! There are just so many fun ways to do things in Excel : ) Happy holidays!
Matt Schoular (3 months ago)
Great video with multiple options. I am looking forward to the next Office 365 update in early 2019. Thanks Mike. Happy Holidays to you and your family.
ExcelIsFun (3 months ago)
You are welcome, Matt! I look forward to the updates in Office 365 and the oncoming revolution in how we build solutions in Excel : ) Happy Holidays to you, your family and the Online Excel Team!!!!
Richard Hay (3 months ago)
Great video as always. I watched the video within an hour after you posted it but I was inspired to comment by Geert's second comment (his reply to your reply). I won't repeat any of his remarks but I agree with all of them. You will grow your viewer base if you regularly post videos which do what he suggests along with the "epic" videos for those of us who want more advanced topics. In fact, I just reminded myself of you video EMT 1316 (alternative ways to look up multiple tables--in case I have the number wrong). Very effective. But I do have another question. What specific method do you use to capture the filtered data pictures in your DAX examples when you are illustrating, for example, row context. Do you use a standard filter method on the original excel fact table or what?
Richard Hay (3 months ago)
ExcelIsFun (3 months ago)
+Richard Hay , I LOVE your story!!!!! And... I really appreciate your Grandma giving this a "Thumbs Hatchet Mitigation Ups"!!!!!! Thanks, Richard : )
Richard Hay (3 months ago)
My grandfather was a skilled machinist for the railroad. He collected tools and his tool shed contained several hundred clean, sharpened, specialized tools of which he was extremely proud. However, when he was called upon to do a new and different household task he always reached for his old trusty, rusty, tired and dirty hatchet. Grandma on day made up a couple of dozen hamburger patties & put them in the deep freeze only to have them freeze together in a large lump. She asked grandpa for help and he grabbed his hatchet. Grandma complained about the rust and dirt, but he said that chopping hamburger was just like "splitting a log". After a few wild and dangerous swings, the patties dutifully fell apart, my grandpa smiled and grandma expressed survivor's relief. About a year later grandpa brought his hatchet-that-would-fix-anything to yet another household challenge. This time the tired head flew off on the first swing and shattered a window. My grandma furiously muttered "I hope your hatchet can fix THAT". I know many excel users who remind me of my grandpa except their go-to "hatchets" are each named after one of the 472 (or so) excel functions. Your "comparison" and your single function (re: Dynamic Array series) videos powerfully mitigate against such "hatchets". To the extent that a goal of a class in excel is "efficient spread sheet construction" these "hatchet mitigating" videos make a substantial contribution to the excel community. My grandmother (who died before excel was born) would subscribe and give a "thumbs up" to such "hatchet mitigation".
ExcelIsFun (3 months ago)
As for those pictures for how the data model and DAX filters - I create all those pictures manually - as I know of no way to view what is going on internally with DAX...
ExcelIsFun (3 months ago)
Yes, I hear what you and Geert and John Borg and others say - and I will make more fun and comparative videos like this one. But as I commented under Geert's comment and as you said, the main contribution that I can make to the worl is with the Epic Videos that show all the hows and whys!!!! : )
DIGITAL COOKING (3 months ago)
office 365 is better than excel obviously; but for DAX, power query VS office 365 I think (if I'm right) its a matter of big and small data so for small data, I'll choose office 365 but for big data (still confused between DAX and power query )...? very good mike !!! those kinds of video open mind (don't know if it's the right words because of my poor English) about excel ******* :)
ExcelIsFun (3 months ago)
Yes, you and a few others have comment on this video that you like this format of video where I compare many different tools in Excel : ) As for DAX and Power Query: 1) DAX is the function language that we use in Power Pivot and Power BI, functions like COUNTROWS, SUMX, CALCULATE, DIVIDE, AVERAGEX, ALL, VALUES; 2) Power Query is a data import-cleaning-transforming tool that is both in Excel and Power BI - and it is also a function language (M Code Function language), but we don't have to usually type out the M Code because we just use the user interface and it writes the code for us.
John Borg (3 months ago)
Amazing!!! Thanks. I would like to thank you for all the videos posted throughout the Year, considering all the editing and the preparation of all the PDF Notes and Home works given for all of us to improve our Excel. Further, with your way of teaching, it has become real FUN to all of us. Thanks Mike :) :) and I surely hope that in 2019 we will have more and more. Best wishes to you and all who follow your channel regularly like me.
ExcelIsFun (3 months ago)
Yes!!! Yes, I will have more and more in 2019!!!! I have big and fun plans for videos and fun in 2019!!! Thank you for the kind words and for your consistent support, John : ) : )
Thanks <3
ExcelIsFun (3 months ago)
You are welcome, Manh!!!! Thanks for your support with your comment, thumbs up and sub : )
Finance in 5 Minutes (3 months ago)
Excel Master!!!
Finance in 5 Minutes (3 months ago)
ExcelIsFun Same here because I am online student of Excel is Fun.😇
ExcelIsFun (3 months ago)
+Finance in 5 Minutes , Great!!!! It is fun to be part of the Online Excel Team with you : )
Finance in 5 Minutes (3 months ago)
i Don't have much knowledge of Excel.I just share my Excel tricks to society for Faster & Easier work in their life.
Finance in 5 Minutes (3 months ago)
ExcelIsFun Merry Xmas
ExcelIsFun (3 months ago)
Glad you like it, Masterful Phone Excel Guy : )
Awesh Bhornya (3 months ago)
Mike, your channel ExcelIsfun is actually fun. You make Excel so interesting and easy. Mr. Excel and Excelisfun are some of my favourite Excel learning channel. Looks like you guys have so much more to give us in Excel. Keep it going and keep giving us many more awesome videos like these.
ExcelIsFun (3 months ago)
Glad the videos are fun for you, Awesh!!!I will keep giving you and others these videos. And you keep helping with comments and Thumbs Ups on each video that you watch : )
Thanks for this EXCEllent video
ExcelIsFun (3 months ago)
You are welcome, Syed!!!! Thanks for your support : )
RRR program (3 months ago)
Good one mikee..
ExcelIsFun (3 months ago)
Glad it is good for you, RRR!!!! : )
Rob Michaels (3 months ago)
I joined the Office 365 Insider Program when you began your new dynamic arrays video series and I'm glad I did. Merry Christmas and Happy Excel New Year to you, Mike !
ExcelIsFun (3 months ago)
Great you have the Dynamic Arrays now, Rob!!!! Happy Holidays : )
Chris Mancinelli (3 months ago)
Thanks Mike, this was awesome!
ExcelIsFun (3 months ago)
Glad it is awesome, Chris!!! Thanks for the support : )
Excel Bear (3 months ago)
you are tempting me too much with office 365! Merry Christmas, Mike~
ExcelIsFun (3 months ago)
Office 365 is the only way to go, now : ) Thanks for your support, Excel Bear!
Teme Gebr (3 months ago)
Thank you, i have learnt a lot from your videos. I have updated many of my work flows & processes in my work place based on the things I learnt from your videos. I have one question. What if I want to count the products or rows for sales above a certain amount? What if I have a sales return on a latter date and make the sales for that product zero and would like to dynamically exclude that product from the final report?
ExcelIsFun (3 months ago)
Not sure of the top of my head. For back and forth dialog to get Excel solutions try this amazing Excel question site: mrexcel.com/forum
Anil Dutt Bhargava (3 months ago)
ExcelIsFun (3 months ago)
Glad it is cool for you, Anil!!!! Thanks for your support : )
Mohamed Chakroun (3 months ago)
5 ways to solve this tricks Excel is absolutelay amazing I think. 3 new solution are caming-up recently (Dax, PQ and on 2018 Dynamic arrays) Thanks mike and hayyu new year 2019 :-) Glad to celebrate my 4th year following the excelisfun channel :-)
ExcelIsFun (3 months ago)
Thank you for following for 4 years, Mohamed!!!! We have many more years to have fun with Excel!!!!
enrique dominguez (3 months ago)
Excellent Mike. Eager to receive 2019 Office 365 update, tons of additional fun coming soon.
ExcelIsFun (3 months ago)
Tons of new fun, enrique!!!!!! Thanks for your support : )
Victor Friesen (3 months ago)
Excel Power !!! Merry Christmas to you and your family Mike !!!
ExcelIsFun (3 months ago)
Yes, Victor!!!!! Excel Power and Happy Holidays : )
Geert Delmulle (3 months ago)
Very nice video on the topic — you should make an entire series like this: performing specific tasks using all 5 methods. Wonderful to see them compared. IMO you’re on to a new format... BTW: my wish for 2019 (apart from getting filthy rich ;-) is: our installation of O365 at work (semi-annual update cycle) gets updated soon, AND the admin settings get changed so we users can update our O365 installations ourselves.
ExcelIsFun (3 months ago)
+Geert Delmulle Thanks for the constructive and helpful feedback, Geert!!!! I will do more of this format : ) It is fun and good!!! But my main video type will be the massive, comprehensive, all-in-one-video, epic learning events for those of us that really want to learn and the details and whys : ) : ) even if they don't get the massive views....
Geert Delmulle (3 months ago)
Yes I have seen many of them. But somehow this video is different: one small topic (not an entire class) short duration and yet you manage to compare no less than 5 methods effectively. I find that really really good. Other YouTubers (like Leila G.) bank on similar formats, where they even split things up in basic and advanced. Those videos get a lot of views (massive). This way you also lower the threshold for viewers to try other methods available in Excel: this video conveyed many messages amongst which: “look: PowerQuery is not that difficult”, and: “look: PowerPivot and DAX are not that difficult”, it’s easy to do a quick side step to those environments. And you know how it goes: once they’ve tasted those new flavors, they’ll be back for more! :-) Now that I think of it: I remember we talked about this before, and you responded that such videos would be way too long. Well, it all depends on the format, and this format is it! Of course, the trick to keep things compact is: doing things in the minimum number of steps. And that’s another thing that you are so good at.
ExcelIsFun (3 months ago)
I hope you get your wish for 2019!!!! I do have a bunch of videos comparing Excel formulas, Excel Standard PivotTables, DAX and Data Model, and Power Query - but I am sure that you have seen many of theme... : ) Happy Holidays and thanks for your support : )
DRSteele (3 months ago)
Now I have REAL EXCEL POWER!. Thanks tons for all your efforts, Mike. Merry Christmas!
ExcelIsFun (3 months ago)
Yes, DRSteele!!!!! Power in this Holiday season : )
Sevag Barsoumian (3 months ago)
Thanks Mike, with Dax and Power Query is very Powerful but I'am with Dynamic Arrays is so flexible i like to see More Video in New Excel Calc. Engine and the amazing new functions in Excel.
ExcelIsFun (3 months ago)
+Sevag Barsoumian , There will be A LOT more because the Dynamic Array Formulas are just so useful : ) Here is the playlist of the 18 videos I have made so far: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx But I think that you have probably already seen these all : )
Sevag Barsoumian (3 months ago)
+ExcelIsFun i love to see more :)
ExcelIsFun (3 months ago)
You are welcome! Many more to come over the coming years : ) P.S. I think I already have about 20 videos posted...
Han Gogo (3 months ago)
ExcelIsFun (3 months ago)
: ) : )

Would you like to comment?

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