Multiple IFs issues

[IoanaStanescu]

Hi Guys

Really need your help!

Please have a look at the attached excel.

I need to create the following alerts on column F:

- if PRODUCT = NS = SITE in both 2020 and 2021 i need this alert - RENEWED
- if PRODUCT = NS = SITE in ONLY in 2021 i need this alert - NEW
- if PRODUCT = NS = SITE in ONLY in 2020 i need this alert - NOT RENEWED

Can I please get your help?

Thanks so much!
Ioana

[AliGW]

Welcome to the forum.

Could you please mock up some results in your file - 10-20 rows will do. Thanks.

[IoanaStanescu]

Hi Ali

So sorry about that!

Done

[samba_ravi]

Try below formula
=IF(SUM((COUNTIFS($C$2:$C$49,$C2,$B$2:$B$49,$B2,$D$2:$D$49,$D2,$A$2:$A$49,{2020,2021})>0)+0)=2,"RENEWED",IF(A2=2020,"NOT RENEWED","NEW"))

[IoanaStanescu]

Hi Samba

This is amazing!

Really appreciate your help, thank you!

You have a great day ahead!!

Ioana

[SanjiVinsmoke]

=IF(SUM((COUNTIFS($C$2:$C$49,$C2,$B$2:$B$49,$B2,$D$2:$D$49,$D2,$A$2:$A$49,{2020,2021})>0)+0)=2,"RENEWED",IF(A2=2020,"NOT RENEWED","NEW")). This did work for me as well, Thanks