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.
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
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)
I have data repeated in columns like below
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
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.
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
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.
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
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
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?
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".
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!!!! : )
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.
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.
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.
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?
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 :-)
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.
+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....
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.
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 : )
+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 : )
Sechskies Eun Ji Won and rookie singers Lee Soo Hyun and Kim Eun Bi performed the third OST single titled "Love Song". The rookies, who are both training to debut in HYWY Entertainments girl group HYWY Girls, joined the veteran to sing about falling in love with an unlikely person. The rhythmic medium temp track is the perfect tune to make your spring days even brighter.
As a child, there was a portrait in our family home in Paris that I always loved. Today, it’s known as Maya with Doll – but to me it was just a portrait of my mother, albeit a remarkable one. “Your grandfather was a painter,” she would say, whenever the subject of the canvas, one of many that hung around the house, came up in discussion. It was only when I began school, and whispers about my heritage started to follow me, that I realised what an understatement that was. My grandfather was far more than a painter. He was the defining figure of 20th-century art – and, as I would learn later from years of academic study, a true genius. It was a revelation that would shape the course of my life in many ways. When Picasso died – in 1973, the year before I was born – he left behind 45,000 works, not to mention personal objects and correspondence.