Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Extract data with helper column in Excel
  • How to get first column number in range in Excel
  • How to use Excel INDIRECT Function
  • How to use Excel TRANSPOSE Function
  • How to retrieve first match between two ranges in Excel

Data Analysis Examples

  • Understanding Anova in Excel
  • How to create dynamic reference table name in Excel
  • Excel Pie Chart
  • How To Create Frequency Distribution in Excel
  • How To Create Pareto Chart in Excel

Data Validation Examples

  • Excel Data validation don’t exceed total
  • Excel Data validation no punctuation
  • Excel Data validation unique values only
  • Excel Data validation allow weekday only
  • Excel Data validation date in specific year

Excel Rank if formula Example

by

This tutorials shows how to rank items in a list using one or more criteria in Excel.

To achieve this you can use the COUNTIFS function.

Formula

=COUNTIFS(criteria_range,criteria,values,">"&value)+1

Explanation

In the example shown, the formula in E5 is:

=COUNTIFS(groups,C5,scores,">"&D5)+1

where “groups” is the named range C5:C14, and “scores” is the named range D5:D14.

The result is a rank for each person in their own group. Note: although data is sorted by group in the screenshot, the formula will work fine with unsorted data.

Worked Example:   Basic numeric sort formula in Excel

How this formula works

Although Excel has a RANK function, there is no RANKIF function to perform a conditional rank. However, you can easily create a conditional RANK with the COUNTIFS function.

The COUNTIFS function can perform a conditional count using two or more criteria. Criteria are entered in range/criteria pairs. In this case, the first criteria restricts the count to the same group, using the named range “groups” (C5:C14):

=COUNTIFS(groups,C5)

By itself, this will return total group members in group “A”, which is 5.

Worked Example:   Highlight duplicate rows in Excel

The second criteria restricts the count to only scores greater than the “current score” from D5:

=COUNTIFS(groups,C5,scores,">"&D5)

The two criteria work together to count rows where the group is A and the score is higher. For the first name in the list (Hannah), there are no higher scores in group A, so COUNTIFS returns zero. In the next row (Edward), there are three scores in group A higher than 79, so COUNTIFS returns 3. And so on.

Worked Example:   Excel Rank with ordinal suffix Example

To get a proper rank, we simply add 1 to the number returned by COUNTIFS.

Reversing rank order

To reverse rank order and rank in order (i.e. smallest value is ranked #1) just use the less than operator (<) instead of greater than (>):

=COUNTIFS(groups,C5,scores,"<"&D5)+1

Instead of counting scores greater than D5, this version will count scores less than the value in D5, effectively reversing the rank order.

Post navigation

Previous Post:

Excel Data validation whole percentage only

Next Post:

How to Use RANK function in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Remove text by position in a cell in Excel
  • How to count line breaks in cell in Excel worksheet
  • How to check cell contains which things in Excel
  • How to calculate Number of Instances in Excel
  • CHAR function: Description, Usage, Syntax, Examples and Explanation
  • Convert date to Julian format in Excel
  • Display Days until expiration date in Excel
  • DATEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • Display the current date in Excel
  • SECOND function: Description, Usage, Syntax, Examples and Explanation
  • TBILLPRICE function: Description, Usage, Syntax, Examples and Explanation
  • NPER function: Description, Usage, Syntax, Examples and Explanation
  • IRR function: Description, Usage, Syntax, Examples and Explanation
  • YIELD function: Description, Usage, Syntax, Examples and Explanation
  • COUPDAYSNC function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles