Chris Stormer

asked on

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

https://www.experts-exchange.com/questions/29220084/Sort-Score-A-Range-of-Values-in-Excel.html#questionAdd

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.

https://docs.google.com/spreadsheets/d/1PeStssumF6D1LrJG1Nhz91QPctYR4lALUUpVZWR6_Vc/edit#gid=0

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

https://www.experts-exchange.com/questions/29220084/Sort-Score-A-Range-of-Values-in-Excel.html#questionAdd

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.

https://docs.google.com/spreadsheets/d/1PeStssumF6D1LrJG1Nhz91QPctYR4lALUUpVZWR6_Vc/edit#gid=0

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

Last Comment

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.

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:

Ranking values with dependent criteria.xlsx

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

ASKER

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?

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

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

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.

ASKER

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.

https://docs.google.com/spreadsheets/d/10YFyzc9EymEYsMkIDKNG90eFOGao2HxGOp2hlV3IdW8/edit#gid=0

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

I've rewritten the question to be more clear.

https://docs.google.com/spreadsheets/d/10YFyzc9EymEYsMkIDKNG90eFOGao2HxGOp2hlV3IdW8/edit#gid=0

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

I need permission to view your latest file.

ASKER

Sorry.

Fixed.

Fixed.

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.

ASKER

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?

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

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

ASKER