Displaying name of Sales Person with max Quantity sold in a Particular category in Excel

Question

Below is how my data looks.

Data :

Sales Person    item_type   id    Week
mo                  calc    8061    30
mo                  calc    8062    30
el                 purse    5692    31
mo                  calc    8064    31
mo                  calc    8065    30
pi                  calc    5696    30
pi                  Calc    8069    31

Now i have dashboard that shows values for last 3 weeks:

   **Current Dashbaord**            
        wk30    wk31    wk32
calc            
Purse   

With data provided above what am trying to do is publish the name of sales person who made most sales on that particular category. i.e calc and purse.

My categories are fixed so no worry on that side and weeks are automated so that also has no issues but trying to publish the name of sales person who made max sales on that category for that week is my stuck area.

Here is how my output will look like

    Output          
        wk30    wk31    wk32
calc    mo       pi 
Purse   "-"      el 

I have reached to a point where i can show total sales for that category in the particular week. Thanks in advance. {:

Answer 1

If there are many items and many salesmen, this will become very cumbersome to do. For just a few items and salesmen, I would suggest the following approach.

See these references COUNTIFS() and INDEX() with MATCH() for more information.

First create a "helper" table for each item and count the number of sales for each salesman and each week:

This formula in G4, filled down and across:

=COUNTIFS($A$2:$A$8,$F5,$B$2:$B$8,$H$2,$D$2:$D$8,G$4)

gives the sales of Calcs by salesman and month. You'll need one of these tables for each item:

Then, this formula, in the cell below "30", filled across:

=IF(MAX(G5:G7)=0,"-",INDEX($F$5:$F$7,MATCH(MAX(G5:G7),G5:G7,0)))

gives the first row of your Output table. A similar formula is needed for each item row.