Date Result returns a "Expired" or "Expiring" or "Current"

[NGB82KS]

I'm sure I'm just missing something in my statement because i get a formula error, but I'm trying to have my Column "i" look at Column "E" which is a date format.
Column "i" would return "Expired" if the date is <today(), "Expiring" if the date is between today and 120 days, and "Current" if its >121 days.

Table 1

Column E	Column I
9/1/2020	EXPIRED (Because its <Today)
10/1/2020	EXPIRING (Because it expires between today and 120 days)
2/1/2021	CURRENT (Because its >120 days)

Then I need to put the output of "i" in another table for a countifs that will count the total

Table 2

Column B	Column C	Column D
SCHOOL A	CURRENT	1 (Would Sum if Table 1 Column "i" matched "current" + "expiring")
SCHOOL A	EXPIRING	3 (Would Sum if Table 1 Column "i" matched "expiring")
SCHOOL A	EXPIRED	5 (Would Sum if Table 1 Column "i" matched "expired")

Any help would be appreciated.

 

[Joe4]

Try this (assuming E2 always has a date):

=IF(E2<TODAY(),"EXPIRED",IF((E2-TODAY())<=120,"EXPIRING","CURRENT"))

 

[Fluff]

For table 1 use

=IF(E2<TODAY(),"Expired",IF(E2<TODAY()+120,"Expiring","Current"))

What column in Table 1 has the name of the school?

 

[NGB82KS]

For table 1 use

=IF(E2<TODAY(),"Expired",IF(E2<TODAY()+120,"Expiring","Current"))

What column in Table 1 has the name of the school?

Coulmn G lists the schools by name

 

[NGB82KS]

Try this (assuming E2 always has a date):

=IF(E2<TODAY(),"EXPIRED",IF((E2-TODAY())<=120,"EXPIRING","CURRENT"))

So that worked, but i do have some blank dates, So i added isblank "

=IF(ISBLANK(E:E),"NA",IF(E:E<TODAY(),"EXPIRED",IF((E:E-TODAY())<=120,"EXPIRING","CURRENT")))

"

 

[Fluff]

Coulmn G lists the schools by name

Ok, how about

=COUNTIFS(Sheet1!G:G,B2,Sheet1!I:I,C2)+IF(B2="Current",COUNTIFS(Sheet1!G:G,B2,Sheet1!I:I,"Expiring"),0)

 

[NGB82KS]

Ok, how about

=COUNTIFS(Sheet1!G:G,B2,Sheet1!I:I,C2)+IF(B2="Current",COUNTIFS(Sheet1!G:G,B2,Sheet1!I:I,"Expiring"),0)

That was it, literllay sat here for 2 hrs looking at websites and videos and you cleared my problem in like 10 Min. Wish i could give More thumbs up and thanks man!

 

[Fluff]

Glad we could help & thanks for the feedback.