Ranking conditional groups... but negative figures...

I need help guys

I'm trying to rank items in a table in multiple groupings (see attached file).

I need to rank the store number (1751 etc) in ranking order of area (R1-A1), as well as region separately (which I'm sure I can do once someone helps me with ranking by area!)

I think I'm getting stuck because of the negative values I'm trying to rank.

Any help would be greatly greatly appreciated

So which would be ranked number 1, the lowest number for each area, e.g. for area R1-A1 that would be store 1754? If so then try this formula in E2 copied down

=SUMPRODUCT((C$2:C$621=C2)*(D$2:D$621<D2))+1

Sorry, no - it's the highest negative number that will be ranked number 1.

it's an excerpt from SAP so negative figures = good thing..

To make it rank the other way just change the < to a >

Thanks, I think it worked! You guys rock.
(well, you guy, anyway... hehe)

I have another ranking question;, I can't get it working I don't know why.

Basically, I want to rank column C (P8W2) within region (column A).

The formula I have is:

=SUMPRODUCT(($A$3:$A$1000=$A3)*($C$3:$C$1000<$C3))+1

Where did I go wrong?

try,

=SUMPRODUCT(--(A$3:A$618=A3),--(B$3:B$618<=B3))-SUMPRODUCT(--(A3:A$618=A3),--(B3:B$618=B3))+1

in e3 and drag down to rank by store.

=SUMPRODUCT(--(A$3:A$618=A3),--(C$3:C$618<=C3))-SUMPRODUCT(--(A3:A$618=A3),--(C3:C$618=C3))+1

in f3 and drag down to rank by column C,

check the columns for any #N/A as these will stop the formula.

thanks reg

Originally Posted by tangcla

Where did I go wrong?

You have a single #N/A in column A. I suggest you delete that then your formula should work.......

Originally Posted by daddylonglegs

You have a single #N/A in column A. I suggest you delete that then your formula should work.......

hehe yes, bingo! That was the source of all my headaches.