Super video, Mike! I messed around and noticed that when you select the contents of the Table (like at 9:19), you can also hover the mouse over the very left of the top left field name and there will appear a down-right diagonal arrow which you can click on. That will insert the entire dataset into the formula.
@@excelisfun yet another o my question, I noticed ur doing 2 vlookups subtraction, what if for learning reason I would want to do that calculation using only 1 formula (like doing the subtraction within VL function? I'm asking cos now in o365 if in column index we type {2\3} the formula will spill 2 results. and now, how can I subtract them? cos with addition we could use basic SUM() function
Always many ways to have fun in Excel, but... the SUMPRODUCT almost always takes much longer to calculate on large data sets. However, before Excel 2007, we had to use SUMPRODUCT when we wanted to rank with more than one condition...
Hi Mike, Great and awesome video. Could you pls help with the fill without formatting while I use Ctrl+D shortcut to fill the formula down? I do not get the option of Fill without formatting when I use ctrl+D option instead of when I fill formula down using angry rabbit. Pls help.
Hi Mike, Thanks for your fantastic knowledge sharing videos. I would like to know how do you zoom workbook and window. Do you use any standard windows tool or third party tool. Please let me know as it would be great help.
Dear Mike, Greetings, Your videos are great.. Thanks for sharing these with us. Could you please help me to solve a sumproduct problem which i have been facing currently. Actually i have two workbooks 1) Database sheet.xlsm (Macro based work book contains tables and userform) 2) Report workbook.xlsx I looking for a formula which can fetch the figure from book 1 and update the workbook 2. I tried sumifs & Sumproduct, both work great, but the problem is both formula dont work if the source file is closed. I tried the below formula. =SUMPRODUCT(--('D:\SHIB\BUDGET 2017\Budget Report 2017 Presentation\PGI INVOICE-ESTIMATE DATABASE.xlsm'!Invoice_List[Expense Main Group]=C4),'D:\SHIB\BUDGET 2017\Budget Report 2017 Presentation\PGI INVOICE-ESTIMATE DATABASE.xlsm'!Invoice_List[INDIAN BRANDED BUSINESS]) I also used VBA on workbook 1 to display userform once open the workbook and the excel sheet. Requesting you, kindly help me on this issue. If you require the actual excel files then please let me know, i will share with you. Thank a lot. Regards Shib
![F.HERO Ft. JSPKK x ลำไย ไหทองคำ x M-PEE - ไม่สนิทบิดหมด (Thai Riders Anthem) [Official MV]](http://i.ytimg.com/vi/kPJZ0UihBfk/mqdefault.jpg)
This was a neat trick using COUNTIFS to generate a "RANKIF" function. Very informative video!
: )
best teacher of all the times,
Thanks Mike, your are a great teacher and enternatainer
Hi mike am big fan of you. Your sample videos and explanation of excel tricks very very good..Amaizing.... Best wishes.
I am glad that the videos help!
Hi Sir, Can you help me for solve one of our report in excel. Also how i can send for you the excel attachment ?
Your videos clearly show the advantages of using Excel Tables and structured table references. And I look forward to the DAX method next.
Yes, Excel Tables are so useful and efficient : ) DAX coming up later in the week!
Super video, Mike! I messed around and noticed that when you select the contents of the Table (like at 9:19), you can also hover the mouse over the very left of the top left field name and there will appear a down-right diagonal arrow which you can click on. That will insert the entire dataset into the formula.
Awesome tip!
@@excelisfun yet another o my question, I noticed ur doing 2 vlookups subtraction, what if for learning reason I would want to do that calculation using only 1 formula (like doing the subtraction within VL function? I'm asking cos now in o365 if in column index we type {2\3} the formula will spill 2 results. and now, how can I subtract them? cos with addition we could use basic SUM() function
Great Great Great....Greta trick with countifs.. Thanks for all ur efforts mike
Ranking with COUNTIFS - awesome! :)
Yes, years ago we used SUMPRODUCT to do this, but now of course, COUNTIFS rocks!!!!
Mike, thanks for another interesting video. As well as COUNTIFS, you could use:
=SUMPRODUCT(--($N$7:$N$22>N7))+1
{=SUM(--($N$7:$N$22>N7))+1}
Always many ways to have fun in Excel, but... the SUMPRODUCT almost always takes much longer to calculate on large data sets. However, before Excel 2007, we had to use SUMPRODUCT when we wanted to rank with more than one condition...
Thank you for your video. I tried using ">=" instead of ">"+1 in COUNTIFS Function and it works well, haha
Yes, but it will not work with ties.
Actually your formula assigns the same values to ties. How is it possible to avoid this issue?
Very good ranking with countifs using ">"1& amazing Mike
Glad the vids help : )
I believe you must be elegantly using excel than the one who created excel :D
you are inventor of Excel.
No, just a guy having fun with Excel : )
Mike, countif for ranking.really handy trick.
Glad you like it : )
Hi Mike, Great and awesome video. Could you pls help with the fill without formatting while I use Ctrl+D shortcut to fill the formula down? I do not get the option of Fill without formatting when I use ctrl+D option instead of when I fill formula down using angry rabbit. Pls help.
Hi Mike, Thanks for your fantastic knowledge sharing videos. I would like to know how do you zoom workbook and window. Do you use any standard windows tool or third party tool. Please let me know as it would be great help.
If I am in Excel or Word or a Browser, I hold Ctrl and Roll the Wheel on the Mouse.
Thank you for your videos! Hey do you have any tutorials on how to create macros or how to use Microsoft access ?
Only a few basic videos.
Hi Mike, love your videos, thanks. The last few have not been focused so its very fuzzy and hard to see clearly.
It may be that they were not fully processed by TH-cam yet. Can you try again now and see if they are fuzzy?
Still fuzzy on my computer
Is anyone else having this problem? I can't seem to simulate it on my end...
Just watched and the video quality looked fine to me. Nice and clear!
Thanks for helping us to pinpoint the issue.
Love it!
Thanks for the love!!!
Nice Trick....
Real Fun!!! :)
Thanks for the realness : )
عالی
Hi Sir, Can you help me for solve one of our report in excel. I want to know, how i can send for you the excel attachment ?
Try posting question to Excel Question site: mrexcel.com/forum
Dear Mike,
Greetings,
Your videos are great.. Thanks for sharing these with us.
Could you please help me to solve a sumproduct problem which i have been facing currently.
Actually i have two workbooks
1) Database sheet.xlsm (Macro based work book contains tables and userform)
2) Report workbook.xlsx
I looking for a formula which can fetch the figure from book 1 and update the workbook 2.
I tried sumifs & Sumproduct, both work great, but the problem is both formula dont work if the source file is closed.
I tried the below formula.
=SUMPRODUCT(--('D:\SHIB\BUDGET 2017\Budget Report 2017 Presentation\PGI INVOICE-ESTIMATE DATABASE.xlsm'!Invoice_List[Expense Main Group]=C4),'D:\SHIB\BUDGET 2017\Budget Report 2017 Presentation\PGI INVOICE-ESTIMATE DATABASE.xlsm'!Invoice_List[INDIAN BRANDED BUSINESS])
I also used VBA on workbook 1 to display userform once open the workbook and the excel sheet.
Requesting you, kindly help me on this issue.
If you require the actual excel files then please let me know, i will share with you.
Thank a lot.
Regards
Shib