Using =SUMPRODUCT to calculate rank

[RobynLouise]

Hi

I have a data set that I need to calculate quintiles on. Essentially, my data set shows:

- the number of transactions taken place in a range of shops, broken down into hourly periods

I want to rank these transactions and calculate a quintile based not just on the time of transaction but also the county in which it takes place. The data looks as follows

Store code (F)	County (G)	9 - 10am (H)	10am - 11am (I)	11am - 12pm (J)
xxxx	Devon	3	9	8
xxxx	Cornwall	7	8	11

I want to be able to show which stores are in the bottom quintile for transactions at a certain time, in their county. I had been using the following formula:

=SUMPRODUCT(($G$3:$G$9196=G3)*(H3>=$H$3:$H$9196))/COUNTIF($G$3:$G$9196,G3) (G relates to county, H relates to transactions per hour)

However I am finding that the result (which is a percentile) is wrong because it is taking blank cells into account (these need to be excluded as the store was closed)

So can anyone help me in excluding blank cells in this formula?

Thank you

 

[AlanY]

try this



=SUMPRODUCT(($H$3:$H$9196>=0)*($G$3:$G$9196=G3)*(H3>=$H$3:$H$9196))/COUNTIF($G$3:$G$9196,G3)

 

[RobynLouise]

Thank but it's still not working!