Ranking for multiple ranges in a column

[VKS]

Hello Experts,

Facing some tough time with Ranking. I have sales data from various outlets those are separated by region. Every region has a subtotal. What I am trying to achieve ranks with in the region and ranks within total network. Please note I have data running down in few hundred rows and I am not too sure how to rank from multiple ranges (Excluding the subtotal line).

Attached, please find a sample file to explain the problem and desired output. I am looking for best possible solution for column E.

Best Regards/VKS

[oeldere]

With an pivot table, see the attached file.

[VKS]

Hello oeldere,
Thanks for your help as many times before.
Do you think we can get both the ranks in the PT i mean getting Rank within region and total ranking.
Best Regards/VKS

[oeldere]

Maybe like this (second pivot table).

See the attached file.

[VKS]

It seems pivot table is not the right solution as i cant sort the locations.
Thanks for your time though.

[oeldere]

Please Login or Register  to view this content.

If this is NOT what you want, please add the result, manualy in the file and post it.

Please reply.

[VKS]

Sir please see the updated file. Hope this is clear enough to explain where am i trying to reach.
Best Regards/VKS

[VKS]

I think i also must add here that column A B and C are Pivot table already & D and E are ranks next to the pivot table but outside pivot table

[Haseeb Avarakkan]

Hello VKS,

Try this in E2, then copy down.

=IF(B2="","",COUNTIFS(B:B,"<>",C:C,">"&C2)+1)

[VKS]

Thanks Haseeb..... Worked Great
My Star goes out to both of you for helping me getting over this.
Wondering if Hasibs solution can be a part of the Pivot Table as calculated field. If not then can you please slightly explain the formula please.
Best Regards/VKS

[oeldere]

@VKS,

You don't want the result asending?

Instead you just need the number of the rank?

[VKS]

Thats right sir coz column a b and c are pivot table that i dont want to touch. My play area is limited to ranks within the region and all regions combined i.e. column D and E (E was the problem area)

[oeldere]

Then I guess it's not possible with pivot table.

[benishiryo]

hi VKS. Haseeb's formula is as such:
If B2 is equals to blank, then show as blank. otherwise, count how many cells are not equals to blank in Column B (B:B,"<>") AND how many cells in column C are greater than C2 (C:C,">"&C2). by counting only those which are not blanks in column B, the Region Totals will not be included when ranking. so in row 2 where the Revenue is 10, only 3 values are greater than it (in C3, C4 & C10). you then need to plus 1 to include the value itself. hope that helps

[VKS]

Thanks a lot Benishiryo for your help.