Excel Magic Trick 1417 Excel Functions Ranking Unit & Total Profit for Products within Manufacturer

See how to use Excel Spreadsheet Formulas to rank profits and profits per unit for both All The Products and Products Within Manufacturer. Also see throughout the video various ways to copy a formula without changing the formatting.
1. (00:11) Introduction
2. (01:23) SUMIFS function to add Profit
3. (02:52) RANK.EQ function to rank profits. Compare and Contrast the functions RANK, RANK.EQ and RANK.AVE
4. (05:36) COUNTIFS function to rank with criteria (multiple conditions), where we want to rank Product Profits within the Manufacturer Name.
5. (08:34) Two VLOOKUP Functions to calculate Profit per unit for each Product
6. (10:25) RANK.EQ function to rank profits per unit
7. (10:53) COUNTIFS function to rank with criteria (multiple conditions), where we want to rank Product Unit Profit within the Manufacturer Name.
8. (11:40) Summary
9.

Related Videos:
Excel Magic Trick 1417: Excel Functions Ranking For Unit & Total Profit for Products within Manufacturer
Excel Magic Trick 1418: PivotTables Ranking For Unit & Total Profit for Products within Manufacturer
Excel Magic Trick 1419: RANKX DAX Function & More: Ranking Profit for Products within Manufacturer
Excel Magic Trick 1420: Ranking in Power Query: Sort, Grouping, Indexes and Custom Columns

[shoeshines]

This was a neat trick using COUNTIFS to generate a "RANKIF" function. Very informative video!

[atiqkhan]

best teacher of all the times,
Thanks Mike, your are a great teacher and enternatainer

[rrrprogram]

Great Great Great....Greta trick with countifs.. Thanks for all ur efforts mike

[dacksonjose]

Hi mike am big fan of you. Your sample videos and explanation of excel tricks very very good..Amaizing.... Best wishes.

[RobMichaels]

Your videos clearly show the advantages of using Excel Tables and structured table references. And I look forward to the DAX method next.

[drsteele]

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.

[trungbui]

Thank you for your video. I tried using ">=" instead of ">"+1 in COUNTIFS Function and it works well, haha

[mohamedchakroun]

Very good ranking with countifs using ">"1& amazing Mike

[ContentedSoul]

Mike, thanks for another interesting video. As well as COUNTIFS, you could use:

{=SUM(--($N$7:$N$22>N7))+1}

[rrrprogram]

I believe you must be elegantly using excel than the one who created excel :D

[amitkumar-ipyv]

Mike, countif for ranking.really handy trick.

[basudevbasak]

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.

[goose]

Thank you for your videos! Hey do you have any tutorials on how to create macros or how to use Microsoft access ?

[Vijay.Agarwal.]

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.

[robertstark]

Hi Mike, love your videos, thanks. The last few have not been focused so its very fuzzy and hard to see clearly.

[Shib_ghosh]

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.

2017\Budget Report 2017 Presentation\PGI INVOICE-ESTIMATE Main Group]=C4), 'D:\SHIB\BUDGET 2017\Budget Report 2017 Presentation\PGI INVOICE-ESTIMATE 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

[dacksonjose]

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 ?