Try this:

JOSEPH 200
MARY 1300
DAVID 1300
ADAMS 1000

=IF(B1="","",SUMPRODUCT(--(B1<B$1:B$4),1/COUNTIF(B$1:B$4,B$1:B$4&""))+1)

Copy down as needed.


--
Biff
Microsoft Excel MVP


"FARAZ QURESHI" wrote in message
...
I have a list of 4 clients:

A
1 JOSEPH
2 MARY
3 DAVID
4 ADAMS

Next I have list of transactions:

A B
11 JOSEPH 200
12 MARY 300
13 DAVID 400
14 ADAMS 300
12 MARY 1000
13 DAVID 900
14 ADAMS 700

So the totals of each client come upto:

JOSEPH 200
MARY 1300
DAVID 1300
ADAMS 1000

I want to devise a formula for the range B1:B4 reflecting the position of
each client as follows:

A B
1 JOSEPH 3
2 MARY 1
3 DAVID 1
4 ADAMS 2

Because Mary & David have the highest amount of transactions they should
be
numbered 1, Adams is the next so 2 and Joseph, being the least, 3.

Had Mary & David possess different highest amounts they would have been
marked 1 & 2, while Adams & Joseph would have been numbered 3 & 4
respectively.

All the help in this regard would highly be appreciated!

Thanx in advance,

Regards

FARAZ