ranking - Excel - Rank date range with similar criteria -


what have

column criteria range 1

column b criteria range 2

column c criteria range 3

column d date range

what want:

column e return ranking number (1,2,3,...) oldest newest of dates appear in column d have same value in 3 criteria columns.

assuming data table a1:d6, array formula in e1:

=match(d1,aggregate(15,6,(($d$1:$d$6)/(($c$1:$c$6=$b$1:$b$6)*($b$1:$b$6=$a$1:$a$6))),row($d$1:indirect("$d$"&3))),0)*((c1=b1)*(b1=a1)) 

you must input formula ctrl+shift+enter, not enter, or not work

this makes use of aggregate function, introduced in excel 2010, not work in earlier versions

dates in rows there not matching criteria in columns b , c return error - can wrap entire formula in iferror return blank, if preferred:

=iferror(match(d1,aggregate(15,6,(($d$1:$d$6)/(($c$1:$c$6=$b$1:$b$6)*($b$1:$b$6=$a$1:$a$6))),row($d$1:indirect("$d$"&3))),0)*((c1=b1)*(b1=a1)),"") 

Comments

Popular posts from this blog

java - nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet Hibernate+SpringMVC -

sql - Postgresql tables exists, but getting "relation does not exist" when querying -

asp.net mvc - breakpoint on javascript in CSHTML? -