Count Products Report: Excel? DAX? Power Query? Office 365? Excel Magic Trick 1533
HTML-код
- Опубликовано: 5 фев 2025
- Download Excel Start Files: excelisfun.net...
Download Excel Finished Files: excelisfun.net...
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: people.highline...
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)
Great video Mike 📹 👍
You are welcome, Nader!!!!
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.
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 : )
The most beautiful thing of this video is not a technique but the way you described pros and cons of each method clearly. I love it.
Thanks a lots Mike.
You are welcome a lot, Nattawut!!! These videos are fun to make and for all of us, if we know all the different tools, we have real power!
I can feel the power, seem like a Jedi. ^^
Just kidding!
Thanks again.
@@nattawut_chatwiriyacharoen No, you are right. We are all like Jedi when we have high levels of Excel Knowledge!
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!
Brilliant! Many thanks, Mike. Explained beautifully, as ever. You intuitively cover my queries as you go along. Uncanny! 😀
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.
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 : ) : )
Excellent Mike. Eager to receive 2019 Office 365 update, tons of additional fun coming soon.
Tons of new fun, enrique!!!!!! Thanks for your support : )
Now I have REAL EXCEL POWER!. Thanks tons for all your efforts, Mike. Merry Christmas!
Yes, DRSteele!!!!! Power in this Holiday season : )
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 !
Great you have the Dynamic Arrays now, Rob!!!! Happy Holidays : )
You're brilliant Mike! ..... This is the magic of Excel, the same result in different ways. Thank you and Happy New Year.
Excellent topic. We all need to count things on data sheets. Thank you.
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 :-)
Thank you for following for 4 years, Mohamed!!!! We have many more years to have fun with Excel!!!!
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.
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!!!!
Another Amazing Video Mike...Can't wait to have the updated office 365... Thank you very much Mike!
You are welcome so much, edgie!!!!! Thanks for your support : )
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.
You are welcome! Many more to come over the coming years : ) P.S. I think I already have about 20 videos posted...
@@excelisfun i love to see more :)
@@sevagbarsoumian516 , 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: ruclips.net/p/PLrRPvpgDmw0nwgWhZjKjqnAwfZBxLcsxx But I think that you have probably already seen these all : )
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 :)
You are welcome! It has been a fun year and next year should be even more fun : )
Excel Power !!! Merry Christmas to you and your family Mike !!!
Yes, Victor!!!!! Excel Power and Happy Holidays : )
you are tempting me too much with office 365! Merry Christmas, Mike~
Office 365 is the only way to go, now : ) Thanks for your support, Excel Bear!
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.
countifs .product count its very good fr me bcoz it is small fsales to understand nd your teaching way too much Excellent.........thnku mr. mike
Glad that way too much EXCELllence helps, vijay!!!!
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.
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 : )
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 RUclipsrs (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.
@@GeertDelmulle 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....
Thanks Mike, this was awesome!
Glad it is awesome, Chris!!! Thanks for the support : )
Nothing short of "Epic"!
Glad it is Epic for you, Esther k! !!!!!! Thanks for your support : )
💐💐💐👍👍👍👍👍👍👍 very informative... Thank you 💐💐💐💐👍👍👍👍
You are welcome!!!!! Thanks for the Thumbs Ups and Flowers!!!!!
Thanks for this EXCEllent video
You are welcome, Syed!!!! Thanks for your support : )
Muchas gracias y Feliz Navidad!!
Thanks, fabry!!!!! Happy Holidays : )
The # technique! Wow! Thanks for the amazing video Mike!
You are welcome, Teammate Kevin : )
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 ******* :)
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.
thank you so much for the video it's really fascinating
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
Thanks, Bart!!!! There are just so many fun ways to do things in Excel : ) Happy holidays!
Thanks for the video! :)
Good one mikee..
Glad it is good for you, RRR!!!! : )
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?
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!!!! : )
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...
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".
@@richardhay645 , I LOVE your story!!!!! And... I really appreciate your Grandma giving this a "Thumbs Hatchet Mitigation Ups"!!!!!! Thanks, Richard : )
:)
excellent information
EXCELlent!!!! : )
😱💛
: ) : )
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?
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
Thanks
You are welcome, Manh!!!! Thanks for your support with your comment, thumbs up and sub : )
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
Excel Master!!!
Glad you like it, Masterful Phone Excel Guy : )
ExcelIsFun Merry Xmas
i Don't have much knowledge of Excel.I just share my Excel tricks to society for Faster & Easier work in their life.
@@simfinso858 , Great!!!! It is fun to be part of the Online Excel Team with you : )
ExcelIsFun Same here because I am online student of Excel is Fun.😇
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
Cool
Glad it is cool for you, Anil!!!! Thanks for your support : )
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
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
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.
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.
For back and forth dialog to get Excel solutions try this great Excel Question Forum: mrexcel.com/forum
I forgot to mention in power query
master