MAX date with conditions

  • MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    Please I need help. Excuse my English, I use Google translator.

    I need a formula to solve:

    I have a data range A2: B15, in column A record dates, in column B the variables X, Y and Z.

    I need to count the occurrences, only of X and Y, and know which is the greater of the two, if the number of greater occurrences is after the last date of the lesser occurrence, the greater occurrence will be shown as a result.


    1- If Σ X> Σ Y first date X> last date Y = X

    2- If Σ Y> Σ Z first date Y> last date Z = Y

    If other <> to 1 or 2 = XX

    I use Excel 2016. Thanks for your help


  • =IF(AND(COUNTIF(B3:B15,D3)>COUNTIF(B3:B15,D4),MIN(IF(B3:B15=D3,A3:A15))>MAX(IF(B3:B15=D4,A3:A15))),D3,D4)

    Good luck!