Using Rank Function but with filtering through another criteria as well.

Hello to anyone who can help and thank you in advance, I would like to populate a table that is based on the Rank of 1 criteria, "Potential Return", but then also specifically for another criteria w...

Formulas and Functions

mtarler
Sep 16, 2020
DCL611 You are not doing anything wrong, your version of Excel doesn't have dynamic arrays (which means you also don't have some of the new functions that come with it like FILTER(), UNIQUE() and some others). So going back to old school tricks I think this should work for you:
=IFERROR(OFFSET(Ranks!$Y$1,AGGREGATE(15,7, ROW(Ranks!$Z:$Z)/(Ranks!$Z:$Z=$AQ8),COUNTIF($AQ$1:$AQ8,$AQ8))-1,0)," ")
see attached. If it works for you I hope you like and mark this as best answer 🙂
VaultInventory1.1.xlsm1.2 MB

mtarler

Silver Contributor

Sep 13, 2020

DCL611 I used the following formula to get what you want but I'm sure there are others that might work also. This does assume you have array formulas in your version of excel:

=INDEX(FILTER(H:H,I:I=C4),COUNTIF(C$1:C4,C4))

I did a similar formula for column E (Return Potential) because I assume you will need that too. I don't know where column D (Size) comes from. I have attached the sheet for you to review. I hope that helps.

DCL611

Copper Contributor

Sep 14, 2020

mtarler Thank you so very much! Just what I was looking for!

Forum Discussion

Using Rank Function but with filtering through another criteria as well.