Conditional Ranking in Excel – RankIF using SumProduct


I was hunting around for a RANKIF function yesterday so I could rank a load of hospitals in terms of their cost per procedure.


Unfortunately RANKIF isn’t one of the available functions but I managed to achieve the same effect by utilising the SUMPRODUCT function.

I wrote a SUMPRODUCT tutorial some time ago indicating how the powerful function can act as a multi conditioned SUMIF formula but it seems it can also act as a multi conditioned RANKIF formula as well.

The formula I used to achieve the above ranking was:


Copied down the column it will return the number of organisations for a given procedure which have costs higher than the selected row. Adding 1 to the result just ensures that the highest cost organisation starts with a rank of 1 rather than 0.


All the worksheets (and some extras) shown in the microsoft excel tutorial below are included in the example spreadsheet, available to download Sumproduct.xls

This is quite a special excel spreadsheet function but the online help available from microsoft is unfortunately pretty poor.

It works as an advanced form of the SUMIF formula where you can sum a range of values based on a number of different criteria.

Here is the simple formula layout or syntax:

=SUMPRODUCT((Condition 1)*(The range to sum))

or slightly more complex:

=SUMPRODUCT((Condition 1)*(Condition 2)*(The range to sum)

the “*” symbol between conditions represents an AND a “+” symbol would represent an OR.

Here is a simple example, of a schools course grades displayed in the following spreadsheet table,

You will probably want to summarise this to show the total number of students passing the course in each grade.


This makes use of the simple SUMPRODUCT formula in cell F2:


the use of absolute referencing here enables the formula to be copied all the way down the table.

In English the spreadsheet formula reads:

Sum the results in column C where the values in column B = “A”

Moving on to a slightly more complex example, we could increase the summary table to show each students performance across the different grades.


This requires the addition of another condition – namely STUDENT equals.

The spreadsheet formula in cell I2, which can again be copied across is:


In English the spreadsheet formula reads:

Sum the results in column C where the grades = “A” and where the student = “Adam”

To see these powerful excel functions in action and utilising the Condition 1 OR condition 2 terminology, download my example microsoft excel spreadsheet.