conditional ranking

Posted on 2012-09-06
Last Modified: 2012-09-08
I am using sumproduct function for conditional ranking.  

But the ranking is not in sequence when there is a repetition of same amounts.

Please find enclosed sample.

Could you please fine tune the formula so that the ranking by the function is matching with the ranking in the example column ?

Thanks in Advance.
Question by:oldrin
    LVL 50

    Expert Comment

    by:barry houdini
    Try this formula in B2 copied down


    regards, barry

    Author Comment

    Thanks Barry,

    Your solution is working and results are as per example.

    When I increase the range to above 73 and if the cells are blank then an error is resulted.

    Could you please help to resolve this ?
    LVL 50

    Accepted Solution

    OK, yes, this version refers to 100 rows but should still work if you have data in fewer than 100


    regards, barry

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now