SUMPRODUCT for ranking with multiple criteria

[HilaryExcel]

I'm using SUMPRODUCT in place of the missing 'RANKIF' option in Excel2010, but there are gaps in the results. Ranking 1, 2, 3 might be populated correctly but there will be no result for ranking 4 and 5 for example. Why is this happening? And can I make a change to avoid this?
Example of the formula:

=SUMPRODUCT(--($Y5=$Y$5:$Y$10000),--($AZ5<$AZ$5:$AZ$10000))+1

Thanks!

 

[steve the fish]

That will rank based on the order of the numbers in AZ and the text in Y. It will 'miss' numbers if there are ties as would RANK.

 

[HilaryExcel]

That will rank based on the order of the numbers in AZ and the text in Y. It will 'miss' numbers if there are ties as would RANK.

Thanks stevethefish.
I've separated one individual criteria and applied RANK to check the SUMPRODUCT was working (and it is). The result with RANK is 1, 2, 2, 2, 2, 6, 7, 8, 9, 10. With SUMPRODUCT I get 1, 2, blank, blank, blank, 6 etc.
Is there another option? Is there any option to return the repeated ranking?

Thank you

 

[steve the fish]

I cant see how you produce blank with that formula. What is the formula you are using?

 

[HilaryExcel]

Sorry, I don't speak excel very well... =SUMPRODUCT(--($Y5=$Y$5:$Y$10000),--($AZ5<$AZ$5:$AZ$10000))+1 returns #N/A instead of the items with repeated rankings. (I just cut out the errors with IFERROR "")

 

[steve the fish]

I really cant see why. If any of the cells in Y5:Y10000 or AZ5:AZ10000 had #n/a then all of the sumproduct formulas would error. What is in Y and AZ where they fail?

 

[HilaryExcel]

Sorry, i'm not explaining well enough. I'll work it out.