Thanks Mike explaining succintly when to use =ROWS() and when =COUNTIFS() by extracting records. Formulas for extracting records you presented in this and many other videos as (EMT 627) tutorial are simply amazing and I am geting slowly behind the whole Facade of array of letters and logical statements in formulas, what it means, what it means when they are divided (filtered) etc. Through exercise it gets much faster to understand the whole Excel logic, but even more faster through your tutorials. Thanks a lot for putting so much effort explaining these two Formula results to the most minute detail. =IF(ROWS(G$10:G10)>$E$7,"",INDEX(C$6:C$19,AGGREGATE(15,6,(ROW($A$6:$A$19)-ROW($A$6)+1)/($C$6:$C$19=$F$7),ROWS(G$10:G10)))) =IF(J10="","",INDEX($C$6:$C$19,AGGREGATE(15,6,ROW($C$6:$C$19)-ROW($C$6)+1/($B$6:$B$19=$J10),COUNTIFS(J$10:J10,J10))))
For me the method you used to explain the syntax in EMT1334 is much more easy to follow. These days you are the most important mentor in my life. THANKS
Hi , i am excel beginner your videos made me advance player in excel . thanks Mike thanks for sharing many videos for us .. as always you are the best mike ..
That formula to extract the name of the top 5 took off a frustration of many years, thank you!! But I would suggest that if you used the hole column C for the INDEX() function you wouldn't need to subtract -ROW($A$5) and the formula would be a little faster.
EXCELlent video, Thank you Mike.... I have one question: What hould I modify if the criteria is only ONE (e.g Salesrep) but the number of Salesrep is more than one (e.g I want to list all sales by Sioux and Chin)?
Are there more basic videos that explain a few of the discrete skills used within this video, such as AGGREGATE and expandable ranges? This just feels a bit beyond my capabilities at the moment. Anyway, love the channel. I just bought your books. I'm getting better at Excel and lately even got a great job, partially due to the fact that I know Excel pretty well. Thanks!
Hi Mike, I hope mary chrismas for you and your familiy too, I have kinda stupid question for you, how use dynamic formula for (filter, let, ... etc) to resolve the problem for the second table ( TOP table)
Dear Mike,Thanks for sharing your video. I have been applying it in my analysis and extraction of voluminous datasets =)If we want to have the flexibility to select either one or both criteria, is there a way for the data to be displayed accordingly? For example, how do we ensure the data will still be displayed if we choose either 'SalesRep or Date' vs. both 'SalesRep & Date'?Thanks!
Mike can you please guide, if I need to pass Microsoft Master/Excel level exam (77-728), then will the book (MOS 2016 Study Guide for Microsoft Excel Expert) help? or any idea from where to prepare and pass the exam.
I do not know - because much of what is tested is not so useful or efficient - and so years ago I just stopped paying attension to the certification process and just made videos about real world working topics.
I wanted to calculate the number of weekends falls between two dates in Excel for that I have created the following array formula: {=COUNT(ROW(INDIRECT(1&":"&DAYS(B2,A2)+1))/(TEXT(ROW(INDIRECT(A2&":"&B2)),"DDD")="Sat"))+COUNT(ROW(INDIRECT(1&":"&DAYS(B2,A2)+1))/(TEXT(ROW(INDIRECT(A2&":"&B2)),"DDD")="Fri"))} Where A2 is the start date B2 is the end date The formula looks long is there any way to enhance it and make it shorter.
Watch my 2 min channel intro that shows the classes that you can take to learn those topics: th-cam.com/video/l1-1aVgFth4/w-d-xo.html You aprobly want the 2 Data Analysis classes shown in this video : )
Hey, can you help me with an excel file that I use in my work? I have a file that I have developed for studies on my work. but I want to transfer data automation to another sheet every time I detects in the main file. Plus I want to make a statistic for every day. I can send you the file two understand it.
Thank you Mike. This is an epic Video I was asking myself just yesterday the differnece between rows and countifs when using Index and Small. Awesome video 😀
Hey, I need a solution for this if you can help me. A2 Contains a sentence () and I want a result in B2 that If A2 contains Target Achieved then B2 should reflect "Complete", If A2 contains Target Failed B2 should reflect "Not Complete". Also, is it possible to count the number of Target Achieved and target Failed Contained cell??/ Is there any way you can help me with this, It's been 2 days I am stuck here. Thanks!!
You are brilliant and i follow up your excel video careful and learned a lot IFERROR(INDEX(A$6:A$19,SMALL(IF(($C$6:$C$19=$F$7)*($A$6:$A$19=$G$7),ROW($C$6:$C$19)-ROW($C$5)),ROW($A1))),"")
I am glad that you like the video, but I taught exactly the opposite of your formula in in my book. As I carefully argued in the book, IFERROR is an inefficient function to use because it has to run the array formula in ever cell, whereas, when you build an alternative logical test, the array formula is run only in cells where the condition is TRUE.
![เปิดบ้าน นาราเครปกระเทย 1 ไร่ ขายขาดทุนหลัก10ล้าน เอามาใช้หนี้!!! l [Nickynachat]](http://i.ytimg.com/vi/0jz84VFWVP4/mqdefault.jpg)
Thanks Mike explaining succintly when to use =ROWS() and when =COUNTIFS() by extracting records. Formulas for extracting records you presented in this and many other videos as (EMT 627) tutorial are simply amazing and I am geting slowly behind the whole Facade of array of letters and logical statements in formulas, what it means, what it means when they are divided (filtered) etc. Through exercise it gets much faster to understand the whole Excel logic, but even more faster through your tutorials. Thanks a lot for putting so much effort explaining these two Formula results to the most minute detail.
=IF(ROWS(G$10:G10)>$E$7,"",INDEX(C$6:C$19,AGGREGATE(15,6,(ROW($A$6:$A$19)-ROW($A$6)+1)/($C$6:$C$19=$F$7),ROWS(G$10:G10))))
=IF(J10="","",INDEX($C$6:$C$19,AGGREGATE(15,6,ROW($C$6:$C$19)-ROW($C$6)+1/($B$6:$B$19=$J10),COUNTIFS(J$10:J10,J10))))
i've been looking for this kind of "IF formula" (instead of IFERROR) for sooooo long....THANKS!!!!
Yes, IFERROR on array formulas can go very slow, and IF can speed things up : )
For me the method you used to explain the syntax in EMT1334 is much more easy to follow. These days you are the most important mentor in my life.
THANKS
Hi , i am excel beginner your videos made me advance player in excel . thanks Mike thanks for sharing many videos for us .. as always you are the best mike ..
Thanks Excelsifun....great clips...start my day with coffee in hand and seeing if there is something new to learn today... always makes the day great
Glad the videos help you start the day!
That formula to extract the name of the top 5 took off a frustration of many years, thank you!!
But I would suggest that if you used the hole column C for the INDEX() function you wouldn't need to subtract -ROW($A$5) and the formula would be a little faster.
I've been looking for this solution so long!!! Thank you so much!! Your lessons are awesome and this one is going to save me so much time!!! amazing!!
Hmmm...how do i get the dates in Sequence highest to lowest without destroying the index?
This is an absolutely amazing video 📹. You are the best Mike 👌
Glad you like it : ) : )
can you please demonstrate how to show 2 names in extract records chart? for example, Chin and Gigi
EXCELlent video, Thank you Mike.... I have one question: What hould I modify if the criteria is only ONE (e.g Salesrep) but the number of Salesrep is more than one (e.g I want to list all sales by Sioux and Chin)?
Are there more basic videos that explain a few of the discrete skills used within this video, such as AGGREGATE and expandable ranges? This just feels a bit beyond my capabilities at the moment.
Anyway, love the channel. I just bought your books. I'm getting better at Excel and lately even got a great job, partially due to the fact that I know Excel pretty well. Thanks!
Yes, I have whole classes about how to learn Excel. Here is the most comprehensive:
th-cam.com/play/PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw.html
really great mike you are really beauti of excelfun
Glad you like my videos, Vijay : )
This is very helpful. Thanks again for sharing!
You are welcome!
Hi Mike,
I hope mary chrismas for you and your familiy too,
I have kinda stupid question for you, how use dynamic formula for (filter, let, ... etc) to resolve the problem for the second table ( TOP table)
Dear Mike,Thanks for sharing your video. I have been applying it in my analysis and extraction of voluminous datasets =)If we want to have the flexibility to select either one or both criteria, is there a way for the data to be displayed accordingly? For example, how do we ensure the data will still be displayed if we choose either 'SalesRep or Date' vs. both 'SalesRep & Date'?Thanks!
Great Video!
Glad you like it, Chris!!!
Beautifully done :)
Cool! : ) Glad you liked it!
Mike can you please guide, if I need to pass Microsoft Master/Excel level exam (77-728), then will the book (MOS 2016 Study Guide for Microsoft Excel Expert) help? or any idea from where to prepare and pass the exam.
I do not know - because much of what is tested is not so useful or efficient - and so years ago I just stopped paying attension to the certification process and just made videos about real world working topics.
I wanted to calculate the number of weekends falls between two dates in Excel for that I have created the following array formula:
{=COUNT(ROW(INDIRECT(1&":"&DAYS(B2,A2)+1))/(TEXT(ROW(INDIRECT(A2&":"&B2)),"DDD")="Sat"))+COUNT(ROW(INDIRECT(1&":"&DAYS(B2,A2)+1))/(TEXT(ROW(INDIRECT(A2&":"&B2)),"DDD")="Fri"))}
Where
A2 is the start date
B2 is the end date
The formula looks long is there any way to enhance it and make it shorter.
plz guide me plz bcz I want to improve my skils so I bcm master of excel nd power query
Watch my 2 min channel intro that shows the classes that you can take to learn those topics: th-cam.com/video/l1-1aVgFth4/w-d-xo.html
You aprobly want the 2 Data Analysis classes shown in this video : )
yeah sir I'll attend it tq Mr super duper hero
@@vijaysahal4556 : )
Hey, can you help me with an excel file that I use in my work? I have a file that I have developed for studies on my work. but I want to transfer data automation to another sheet every time I detects in the main file. Plus I want to make a statistic for every day. I can send you the file two understand it.
Thanks you so so so mache :)
what is the alternative for Excel 2007?
Same.
@@excelisfun Hi I have download the workbook from your link, that file wont work either, the returned cell gave "#NAME?"
Thank you Mike. This is an epic Video I was asking myself just yesterday the differnece between rows and countifs when using Index and Small. Awesome video 😀
Hey, I need a solution for this if you can help me. A2 Contains a sentence () and I want a result in B2 that If A2 contains Target Achieved then B2 should reflect "Complete", If A2 contains Target Failed B2 should reflect "Not Complete". Also, is it possible to count the number of Target Achieved and target Failed Contained cell??/
Is there any way you can help me with this, It's been 2 days I am stuck here.
Thanks!!
I think the FIND function will do the job.
You are brilliant and i follow up your excel video careful and learned a lot
IFERROR(INDEX(A$6:A$19,SMALL(IF(($C$6:$C$19=$F$7)*($A$6:$A$19=$G$7),ROW($C$6:$C$19)-ROW($C$5)),ROW($A1))),"")
I am glad that you like the video, but I taught exactly the opposite of your formula in in my book. As I carefully argued in the book, IFERROR is an inefficient function to use because it has to run the array formula in ever cell, whereas, when you build an alternative logical test, the array formula is run only in cells where the condition is TRUE.
@@excelisfunI agree with you 💯
11:30