Combining formulas for multiple countries eg =IF(AND(Country="USA",(TaxID<>"")),"Registered","Not Registered")

[rando123]

Hi All

My company has an extremely convoluted tax ID process and I've demonstrated in table below what the report looks like. I can't change any of the columns but trying to create a desired column G if possible, which is based off the information in the other columns.

Basically I'm having issues combining formulas. I can think of the formula for each situation but just not sure how to nest/combine them together or if that is possible. Anyone know the structure or could help ?

1st situation: There are some countries, like UNITED STATES, COLOMBIA, RUSSIAN FEDERATION, where IF tax ID is NOT blank, Final Registration Status = Registered.

=IF(AND($B2="UNITED STATES",($C2<>"")),"Registered","Not Registered")

2nd situation: Special Country like Australia, Column E = YES, if YES Final Registration Status = Registered. If Verified this means that country is registered regardless of other columns.

=IF(AND($B2="AUSTRALIA",($E2="YES")),"Registered","")

3rd situation: Special Country like Canada, Verified Column E = YES, if Column D = NO, then Not Registered. If Column D = YES, then Registered.

=IF(AND($B2="CANADA",($D2="YES")),"Registered","Not Registered")

	A	B	C	D	E	F	G
1	Company	Country Name	Tax ID (Optional)	Registered (Australia and Canada Only)	Verified (Australia and Canada Only)?	Other Countries Registered	Desired Column "Final Registration Status"
2	Company A	UNITED STATES	123123123			Registered	Registered
3	Company B	COLOMBIA	09992222222			Registered	Registered
4	Company C	AUSTRALIA	12312312322	YES	YES		Registered
5	Company D	CANADA		NO	YES		Not Registered
6	Company E	RUSSIAN FEDERATION	51984988489			Registered	Registered
7	Company F	CANADA	99999999999e	YES	YES		Registered

 

[etaf]

so for the country Australia & Canada , to show registered isn't it JUST column D = YES that's the decision, or does both D & E have to be YES
FOR ALL OTHER COUNTRIES, just need an ID column C to be NON blank

=IF ( AND ( OR( B2 <> "AUSTRALIA" , B2 <> "CANADA") , C2 ="" ) , "NOT REGISTERED" , IF ( AND ( OR( B2 <> "AUSTRALIA" , B2 <> "CANADA") , C2 <>"" ) , "REGISTERED" , IF ( AND ( D2 = "YES", E2 = "YES" ) , "REGISTERED", "NOT REGISTERED" )))