Summing a ranking of several categories

[Guest]

I have looked at the rank function, but I do not think that it is the
function for what I need.

I have a list of salesmen, four in total, that sell three different types of
merchandise.

How would I rank number one through four, based on how they sold each of the
six different types of merchandise.

Example:

Salesman 1,6,18,4
Salesman 2,11,0,3
Salesman 3,2,5,20
Salesman 4,1,3,2

To complicate matters each set of merchandise categories is sorted
individually by amount sold.

Merchandise #1
Salesman 2 11
Salesman 1 6
Salesman 3 2
Salesman 4 1

Merchandise #2
Salesman 1 18
Salesman 3 5
Salesman 4 3
Salesman 2 0

Merchandise #3
Salesman 3 20
Salesman 1 4
Salesman 2 3
Salesman 4 2

Total Sales and Ranking
Salesman 1 28
Salesmen 3 27
Salesmen 2 14
Salesmen 4 6

So what I am looking for is a formula that will put the combined total by
the right salesman, and when I sort by using Data-Sort, each salesman keeps
its correct total.

Steve

 

[Bernard Liengme]

We need a fuller explanation of how the data is stored and what you want to
see
best wishes

 

[Guest]

Well I as re-read my post I see that I myself was not clear. So let me try
again.

The different types of merchandise categories are in columns across the top
of the worksheet. Columns B through D. The different salesmen are list in
column A. Rows A2 through A5.

So at the end of each month I tally up how many each salesman sells of each
piece of merchandise, see example A. Then on another page, for my boss I
would like to rank them in order of total sales. That was what I was trying
to say in my first post. As each of the individual categories is sorted to
show from high to low, the salesman selling the most is in the number one
position, and the salesman selling the least is in the number four position.
This will be done for each of the different categories, see example B. Then
I want to have an overall ranking section where I rank each salesman by
overall total sales, see example C. This last step is where I am having my
problems. I can not seem to come up with a formula that will work. See in
example C, where beside salesman #2, the overall ranking number is 2.00; it
is that number that is causing me problems. Right now I am doing everything
by hand, and I want to speed up the process with a formula. So what I want
my formula to do is find salesman2, and total up his ranking in each of the
different categories shown in example B. Then display that number, i.e.
2.00, beside his name, along with the total sales amount for each salesman.

Example A:
Salesman1 6,18,4
Salesman2 11,0,3
Salesman3 2,5,20
Salesman4 1,3,2

Example B:
Merchandise #1
Salesman2 11
Salesman1 6
Salesman3 2
Salesman4 1

Merchandise #2
Salesman1 18
Salesman3 5
Salesman4 3
Salesman2 0

Merchandise #3
Salesman3 20
Salesman1 4
Salesman2 3
Salesman4 2

Example C:
Salesman1 (2+ 1+2)/3=1.67 28
Salesmen3 (3+2+1)/3=2.00 27
Salesmen2 (1+3+2)/3=2.00 14
Salesmen4 (4+2+4)/3=3.33 6

 

[Bernard Liengme]

Please confirm you have:
in A2: Salesman 1
in B2: 6
in C2: 18
in D2: 4
rather than in A2: Salesman1,6, 18,4

If the later please experiment by selcting A2:A5 and using the command Data
| Text to Column
to separate all the items. Then we can get down to business.

best wishes

 

[Guest]

One way which seems to get you there ..

Illustrated in this sample:
http://www.flypicture.com/download/ODAzMQ==
Multi product rank n average.xls

Source data as posted within A25,
col A = salesman#
cols B to D = sales for merchandise1,2,3

In F2: =IF(B2=0,"",RANK(B2,B$2:B$5))
Copy F2 to H2

In I2: =AVERAGE(F2:H2)
In J2: =SUM(B22)
Select F2:J2, copy down to J5

Cols F to H returns the ranking of sales by merchandise#
Col I returns the average ranking
Col J is the total sales