Forum Discussion
Using Rank Function but with filtering through another criteria as well.
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 🙂
Unfortunately I am not familiar with the “Filter” function. When I type it into my formula window populates with the following:
“FILTERXML Returns specific data from the XML content using the specific XPath”
Am I doing something in correctly?
My version of Excel is the Office Home & Business 2019, is it possible my version is not adequate or am I simply missing something when trying to apply the formula?
When I pull the version up that was adjusted this is what appears in the Function window:
=IFERROR(INDEX(_xlfn._xlws.FILTER(Ranks!Y:Y,Ranks!Z:Z=AQ8),COUNTIF(AQ$8:AQ8,AQ8))," ")
Once again I truly apologize for any neophyte questions or actions but I simply am not having luck applying the formula when using “Filter” without having the “ _xlfn._xlws.” in there preceding “Filter”.
Thank you
Best
David
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 🙂
I found a VERY Simple formula to address this:
https://exceljet.net/formulas/rank-if-formula=COUNTIFS($V8:$V1958 <<Criteria Range (Obviously infinite, since this is COUNTIFS)>>,V9<<Criteria1>>,$AJ$8:$AJ$1958 <<Values Range>>,"<="&AJ9<<Current Row>>)
