# Use Rank With Prerequisits in Excel

Building on a previous question I had where I wanted to rank the results in excel here:

I am not interested in ranking based on a group.  In my example I am calling this Class so what is the rank of a value for that class of numbers.

Is there a way to have the ranking be for a group of values?
Microsoft ExcelMicrosoft Office

Last Comment
byundt
byundt

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Chris Stormer

Thank you!  yes I made a mistake. :)
byundt

The attached file is working in Excel (Microsoft 365).

I couldn't use the RANK & FILTER formula shown above in Excel (though it did work in Google Sheets). As a workaround, you may use either of:
``````=1+SUMPRODUCT((C\$2:C\$7<C2)*(A\$2:A\$7=A2))
=1+COUNTIFS(C\$2:C\$7,"<" & C2,A\$2:A\$7,A2)
``````
The attached workbook shows the formulas in action.
Ranking values with dependent criteria.xlsx
Chris Stormer

Thanks so much Byundt.  Will this all still work this way if what is in column C a word instead of a number.

For example instead of 5, I have the word technology?
byundt

RANK only works with numbers. SUMPRODUCT and COUNTIFS will work with text, but will alphabetize rather than rank based on word count.

byundt

You probably need to post another workbook with sample data and expected results. The data ought to include two different criteria values, duplicate values for at least one of column C value and column A criteria. And for sure you need to post the desired results.

Chris Stormer

I should have started with the most complex version that I was trying to get to but I figured if you guys got me started I would be able to figure it out.

I've rewritten the question to be more clear.

My goal is to be able to rank the values column by sector.

byundt

I need permission to view your latest file.
Chris Stormer

Sorry.
Fixed.
SOLUTION
byundt

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Chris Stormer

ok, I think I misunderstood an earlier comment

One last thing, if I want to reverse the order of the ranking using SUMPRODUCT is that possible?
SOLUTION
byundt

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY