< Go Back

Rank with criteria

If you wish to rank some objects with criteria in excel then it would be appropriate for you to use the COUNTIF function of excel. It is a very convenient way for ranking a larger set of data. Even though excel offers you a RANK function, but this only works with numeric values. Thus, we can use the COUNTIF function as it has the ability to perform two or more conditional counts. It can also be used for logical operators and wildcards.

COUNTIF Function

Syntax

= COUNTIF(range, criteria)

Arguments 

  • Range – It is the range of the cells that have to be counted.
  • Criteria – It checks that which cells have to be counted.

While using the COUNTIF function as an alternative to RANK (), the very first step that we have to do is to find out the alphabetic rank of the data in every given column. It counts the number of cells in a range which are matching with the criteria which have been provided. It should be noted that non-numeric criteria should be enclosed within double quotes “…” but this is not necessarily required with the numeric criteria.

Generic formula

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

Example

Figure 1. Example of COUNTIFS

Here, in the example, we take the students along with their scores. The named ranges are a group from C5:C14 and score D5:D14. Both the criteria’s work together to count the same group.

As in this case, In E5 the formula returns 1 as Deepti is 1st in her group. Similarly, in E6 the formula returns 4 as Lilly has three more scores above her score. Thus, COUNTIFS returns 3. By adding 1 to the COUNTIF result we can make our task easier and achieve proper ranks.

So this was the tutorial for ranking with criteria using COUNTIFS. We hope that this post would help you in the future.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar