Rank if

Hi all, I need some help with my rank function.

I need to rank the value in BA4 against the values in BA4:BA4000

That part is easy enough, but I only want to rank the values in BA4:BA4000 where the value of AF4 is found in AF4:AF4000.

Basically a function that says rank value BA4 against values in BA4:BA4000 where the value in AF4 is also found in AF4000

Hope this is easy enough to understand?

Please Login or Register  to view this content.

Hi,
I am new to this forum & thought I ll post it here rather than start a new thread.
I have revenue in Column A that needs to be ranked from 1 - 1000. However, it needs to be ranked depending on time period. In column B, I have the time period from 2010 - 2012. I need to only rank those revenues that belong to 2012. I cant think of a way of modifying the sumproduct formula. Any help will be greatly appreciated.

Rgds

Originally Posted by daddylonglegs

I assume the intent is to rank by category, that could be done using this formula in row 4 copied down

=SUMPRODUCT((BA$4:BA$4000>BA4)*(AF4=AF$4:AF$4000))+1

change > to < to rank ascending

@quickgun

post your question in a new threat.

You get better help if you post an Excel-example of your workbook, without confidential information.

@ quickgun, I agree with oeldere, you could've posted your problem in a new thread instead.

However, few quick steps to your problem:-
1. Apply filter or format your data as table, now filter 2012 values. (you can also apply Pivot)
2. Further you can sort your data in column B by values (Largest to smallest).
Apply Sl. Nos. in adjacent column, and you have your ranks.

Thanks Oeldere & Fahads_neo. I got my answer. Used Sumproduct for this purpose.