This was a neat trick using COUNTIFS to generate a "RANKIF" function. Very informative video!
@excelisfun4 жыл бұрын
: )
@ContentedSoul7 жыл бұрын
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}
@excelisfun7 жыл бұрын
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...
@pmsocho7 жыл бұрын
Ranking with COUNTIFS - awesome! :)
@excelisfun7 жыл бұрын
Yes, years ago we used SUMPRODUCT to do this, but now of course, COUNTIFS rocks!!!!
@dacksonjose5557 жыл бұрын
Hi mike am big fan of you. Your sample videos and explanation of excel tricks very very good..Amaizing.... Best wishes.
@excelisfun7 жыл бұрын
I am glad that the videos help!
@dacksonjose5557 жыл бұрын
Hi Sir, Can you help me for solve one of our report in excel. Also how i can send for you the excel attachment ?
@rrrprogram86677 жыл бұрын
I believe you must be elegantly using excel than the one who created excel :D
@drsteele47497 жыл бұрын
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.
@excelisfun7 жыл бұрын
Awesome tip!
@ExcelInstructor3 жыл бұрын
@@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
@atiqkhan96374 жыл бұрын
best teacher of all the times, Thanks Mike, your are a great teacher and enternatainer
@rrrprogram86677 жыл бұрын
Great Great Great....Greta trick with countifs.. Thanks for all ur efforts mike
@RobMichaels17 жыл бұрын
Your videos clearly show the advantages of using Excel Tables and structured table references. And I look forward to the DAX method next.
@excelisfun7 жыл бұрын
Yes, Excel Tables are so useful and efficient : ) DAX coming up later in the week!
@MDSHAHID-sy5yi7 жыл бұрын
you are inventor of Excel.
@excelisfun7 жыл бұрын
No, just a guy having fun with Excel : )
@mohamedchakroun49737 жыл бұрын
Very good ranking with countifs using ">"1& amazing Mike
@excelisfun7 жыл бұрын
Glad the vids help : )
@trungbui89667 жыл бұрын
Thank you for your video. I tried using ">=" instead of ">"+1 in COUNTIFS Function and it works well, haha
@excelisfun7 жыл бұрын
Yes, but it will not work with ties.
@pollonero36 жыл бұрын
Actually your formula assigns the same values to ties. How is it possible to avoid this issue?
@amitkumar-ip6yv7 жыл бұрын
Mike, countif for ranking.really handy trick.
@excelisfun7 жыл бұрын
Glad you like it : )
@basudevbasak3092 жыл бұрын
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.
@Vijay.Agarwal.12347 жыл бұрын
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.
@excelisfun7 жыл бұрын
If I am in Excel or Word or a Browser, I hold Ctrl and Roll the Wheel on the Mouse.
@goose86087 жыл бұрын
Thank you for your videos! Hey do you have any tutorials on how to create macros or how to use Microsoft access ?
@excelisfun7 жыл бұрын
Only a few basic videos.
@entertainmentgalaxy9717 жыл бұрын
Nice Trick....
@robertstark3967 жыл бұрын
Hi Mike, love your videos, thanks. The last few have not been focused so its very fuzzy and hard to see clearly.
@excelisfun7 жыл бұрын
It may be that they were not fully processed by KZbin yet. Can you try again now and see if they are fuzzy?
@robertstark3967 жыл бұрын
Still fuzzy on my computer
@excelisfun7 жыл бұрын
Is anyone else having this problem? I can't seem to simulate it on my end...
@brianxyz7 жыл бұрын
Just watched and the video quality looked fine to me. Nice and clear!
@excelisfun7 жыл бұрын
Thanks for helping us to pinpoint the issue.
@Barhomopolis7 жыл бұрын
Love it!
@excelisfun7 жыл бұрын
Thanks for the love!!!
@zaighamuddinfarooqui17057 жыл бұрын
Real Fun!!! :)
@excelisfun7 жыл бұрын
Thanks for the realness : )
@hosseinhosseinpoor95612 жыл бұрын
عالی
@dacksonjose5557 жыл бұрын
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 ?
@excelisfun7 жыл бұрын
Try posting question to Excel Question site: mrexcel.com/forum
@Shib_ghosh7 жыл бұрын
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