I am trying to write an array formula that ranks Column A and partitions the rank by columns B and C but I can't work out how to do it. Essentially I want RANK() OVER (PARTITION BY B,C ORDER BY A DESC)
.
I currently have the array formula below in column D that works for ranking Col A and partitioning by Col B, but I can't work out how to make the formula partition by B and C:
=ArrayFormula(if(B2:B<>"",vlookup(B2:B&"|"&A2:A,{sort(B2:B&"|"&A2:A,B2:B,true,A2:A,false),row(B2:B)},2,false)-vlookup(B2:B,{sort(B2:B,B2:B,true),row(B2:B)},2,false)+1,""))
I would like to rank the each person on each activity (in Column E). Column D shows the formula I have which only gets the most recent time for each person.