COUNTIF, May be?

I have huge data set that are being updated every month. The data include members names and ids and dates (enrolled to program). I need to assign New, Active, Inactive status to each member based in some criterion. The purpose, logic and sample data are all included in the attached file.

I very much appreciate any help on this.

Thank you in advance,

Helal

A solution using COUNTIFS. Let me know if this works for you.

Solved

Ken, worked like charm. Thank you so much for the formula.