To calculate rankings, people often think of using the RANK function. But can RANK compute rankings with conditions? It seems not. Which function should be used instead?
First, let's get to know the RANK function:
The RANK function is a ranking function, most commonly used to find the rank of a value within a range.
RANK function syntax: RANK(number, ref, [order])
- number: the value whose rank is required.
- ref: the reference range for ranking.
- order: 0 or 1. 0 (default, can be omitted) ranks from largest to smallest; 1 ranks in ascending order.
First, let's get to know the SUMPRODUCT function.
- This function is regarded as an all-purpose calculation tool. Given limited space, we'll only cover the core concepts. Here's the conclusion — remember it; a detailed explanation will come later.
- The universal SUMPRODUCT formula is: =SUMPRODUCT((condition1)*(condition2)*...*sum_range)
- It supports single- and multiple-condition summing. Therefore, in this example the expression inside SUMPRODUCT sums based on a given condition.
=SUMPRODUCT(($G$2:$G$33>G2)*($B$2:$B$33=B2))+1The above formula has two conditions:
- $G$2:$G$33>G2 finds how many people's sales are greater than your own. For example, if 100 sales values are larger than yours, your rank is 100 + 1 = 101st.
- $B$2:$B$33=B2 narrows the range further, comparing only where Salesperson = your name.
