Ranking data based on data in other cells

[Pelham]

I have a table of data in a worksheet called 'May 2009' which is not
the same worksheet where I am trying to enter the formula. In the
worksheet where I am trying to enter the formula, I want to rank the
numeric data (rental income) located in O4:O25 of the worksheet called
'May 2009' from highest to lowest as long as there is no blank entry
in the corresponding C4:C25 (tenant name) of the worksheet called 'May
2009'.

There is one catch to this, where the same tenant appears twice in
C4:C25 of the worksheet called 'May 2009', I want that tenant's rental
income from O4:O25 to appear as one 'summed' figure in my ranking.

Any ideas?

 

[Pete_UK]

List your unique tenants in column A of your summary sheet, then use
this formula in B1:

=SUMIF('May 2009'!C$4:C$25,A1,'May 2009'!O$4:O$25)

and copy this down for as many tenants as you have in column A. Then
you can use this formula in C1:

=RANK(B1,B$1:B$21)

Adjust the range to suit, then copy down as far as you need to.

Hope this helps.

Pete

 

[Pelham]

List your unique tenants in column A of your summary sheet, then use
this formula in B1:

=SUMIF('May 2009'!C$4:C$25,A1,'May 2009'!O$4:O$25)

and copy this down for as many tenants as you have in column A. Then
you can use this formula in C1:

=RANK(B1,B$1:B$21)

Adjust the range to suit, then copy down as far as you need to.

Hope this helps.

Pete





- Show quoted text -

Thank you, Pete - it works fine! Trust an Englishman to help out an
Australian!

Once your formulae are in palce and working I use the ranking column
(column C) to order the summary sheet from 1 to 10 so that I can list
the Top 10 tenants. However, is there any way I can combine the two
formulae so that I only have one column (ie column B) which will
automatically order the tenants from 1 to 10 based on rental income?
Furthermore, is there a way that I can have the formula get the unique
tenant names column A for me without my having to enter them myself?

 

[Pete_UK]

Well, it's a bit late here, so hopefully someone else will join in
while I'm having some sleep !! <bg>

Basically, yes, you can sort by formulae. If no-one else jumps in,
then do a Google search of these Excel groups looking for Auto Sort,
or Sort by Formula and you might get some steers in the right
direction.

As regards the unique list of tenants, I thought that as you only have
a range of 21 cells then that wouldn't be too onerous to do it
yourself. Besides, your tenants are not likely to change very
frequently, are they?

Pete