Too many IF statements Excel / VBA

I have added some sample data in this file to explain my problem a bit better. I have blended out the columns which have no relevance to the formula: https://docs.google.com/spreadsheets/d/1_lq0mYPF2ICiFgehQz_YkOa28JUyamG4G8ObV957onY/edit?usp=sharing

My initial file has one row per contract. I want to end up with one row per customer ID. I need to only keep the most recent contract, so the contract with the end date closest to Now(). If one customer ID has two contracts ending at the same time, I would like to keep the row with the higher monthly invoice amount.

I have been doing this with IF statements but there must be a more efficient way. At the moment the highest number of contracts a customer has with 3, but this will increase in the coming months and my if statement method will no longer be viable.

Here is my current formula.

Range("P2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(OR(Status=""in renewing process"",Status=""first contract still running""),""Not Eligable"",IF(Count_of_ID=1,""keep"",IF(Count_of_ID=2,(IF(AND(RC[-15]=R[1]C[-15],MIN(RC[-1],R[1]C[-1])=RC[-1]),""keep"",""delete"")),IF(Count_of_ID=3,IF(AND(RC[-15]=R[1]C[-15],RC[-15]=R[2]C[-15],MIN(RC[-1],R[1]C[-1],R[2]C[-1])=RC[-1]),""keep"",""delete"")))))"

Range("P3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(OR(Status=""in renewing process"",Status=""first contract still running""),""Not Eligable"",IF(Count_of_ID=1,""keep"",IF(Count_of_ID=2,(IF(AND(RC[-15]=R[1]C[-15],MIN(RC[-15],R[1]C[-15])=RC[-15]),""keep"",IF(Count_of_ID=2,(IF(AND(RC[-15]=R[-1]C[-15],MIN(RC[-15],R[-1]C[-15])=RC[-15]),""keep"",""delete"")))),IF(Count_of_ID=3,IF(AND(RC[-15]=R[1]C[-15],RC[-15]=R[2]C[" & _
        "-15],MIN(RC[-15],R[1]C[-15],R[2]C[-15])=RC[-15]),""keep"",IF(Count_of_ID=3,IF(AND(RC[-15]=R[1]C[-15],RC[-15]=R[-1]C[-15],MIN(RC[-15],R[1]C[-15],R[-1]C[-15])=RC[-15]),""keep"",""delete""))))))))" & _
        ""

Range("P4").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(OR(Status=""in renewing process"",Status=""first contract still running""),""Not Eligable"",IF(Count_of_ID=1,""keep"",(IF(AND(Count_of_ID=2,RC[-15]=R[1]C[-15],MIN(RC[-1],R[1]C[-1])=RC[-1]),""keep"",IF(AND(Count_of_ID=2,RC[-15]=R[-1]C[-15],MIN(RC[-1],R[-1]C[-1])=RC[-1]),""keep"",IF(AND(Count_of_ID=3,RC[-15]=R[1]C[-15],RC[-15]=R[2]C[-15],MIN(RC[-1],R[1]C[-1],R[2]C" & _
        "[-1])=RC[-1]),""keep"",IF(AND(Count_of_ID=3,RC[-15]=R[1]C[-15],RC[-15]=R[-1]C[-15],MIN(RC[-1],R[1]C[-1],R[-1]C[-1])=RC[-1]),""keep"",IF(AND(Count_of_ID=3,RC[-15]=R[-1]C[-15],RC[-15]=R[-2]C[-15],MIN(RC[-1],R[-1]C[-1],R[-2]C[-1])=RC[-1]),""keep"",""delete""))))))))" & _
        ""
    Range("P4").Select
    Selection.AutoFill Destination:=Range("P4:P" & Lastrow)

Any help much appreciated!