Formula to find position with multiple conditions

[arnorian]

Hi,

I'm running analysis of data that includes delivery times for two distinct store types (stores under 3000 and over 3000). Each delivery is distinguished by "Run" which can repeat for different regions (see example below). What I need to find is relative position of stores that are over 3000 within each run. Below is the example of what I want to populate in "Time Position" column. I am not sure if there's nested formula is best for it with multiple conditions or some kind of macro code. Once all positions are populated I can then just PivotTable it to just hone in on stores# over 3000 and what their positions are for each run.


Region Run Store Number Time Position
SF, CA 1 2 12:00 AM 1
SF, CA 1 3 2:00 AM 2
SF, CA 1 12 12:00 AM 3
SF, CA 1 3000 2:00 AM 4
SF, CA 1 9333 5:00 AM 5
SF, CA 2 123 6:30 AM 1
SF, CA 2 3123 7:30 AM 2
SF, CA 2 79 10:00 AM 3
SF, CA 2 58 7:00 AM 4
ATL, GA 1 3965 8:00 AM 1
ATL, GA 1 258 9:30 AM 2
ATL, GA 1 9 5:00 AM 3

 

[SpillerBD]

... I think RANK will do it but will have to figure out array part which probably needs a SUMPRODUCT formula plugged in.
bump.

 

[SpillerBD]

I couldn't find an appropriate array formula to plug inside the RANK function.
Did you consider using Pivot Tables to organize and sort your data? I expected an easier use of how by Rank, but finally found https://blogs.office.com/2010/11/02/add-rank-to-pivottable/ which guided me on how to add the rankings. Though those steps shouldn't be necessary IMHP