Conditional IF based on country

[zinah]

Hi,

I have below table that calculations depends on the country, if it's US or Canada, then there will be specific formula but if not, then other formula. I need a formula that I can use to replace below manual formulas that I have to update every time based on the country field. Any suggestions?

Different formula US&CA.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2	Country	Amount	Calc	US and Canada is a differnet formula, rest of the countries is the same			Employee Country	Country Code	Currency Code	Company	%1	%2	Field1	Field2	Field3	Field4	Field5	Field6	Field7	Field8
3	United States of America	319,255.90	14,488.14	=IF(B3>$M$3,((B3*$K$3)+($M$3*$L$3)),(B3*($K$3+$L$3)))			United States of America	USA	USD	1	1.683%	6.200%	147,000	18,243	18,243	-	-	6,500	-	-
4	United States of America	257,600.43	13,450.27	=IF(B4>$M$3,((B4*$K$3)+($M$3*$L$3)),(B4*($K$3+$L$3)))			United States of America	USA	USD	2	1.683%	6.200%	147,000	18,243	18,243	-	-	6,500	-	-
5	Canada	210,000.73	12,649.01	=IF(B5>$M$3,((B5*$K$3)+($M$3*$L$3)),(B5*($K$3+$L$3)))			Canada	CAN	CAD	3	1.950%	7.662%	61,900	6,351	4,979	3,731	2,925	-	4,035	4,035
6	Chile	$60,556.61	-	=B6*VLOOKUP(A6,$G$3:$T$11,5,FALSE)			Chile	CHL	CLP	4	-	-	-	########	5,065	########	2,401	-	788	788
7	Guatemala	$238,608.29	25,841.83	=B7*VLOOKUP(A7,$G$3:$T$11,5,FALSE)			Guatemala	GTM	GTQ	5	10.830%	-	-	50,952	6,589	47,953	6,201	-	-	-
8	Argentina	93674.99	22,530.54	=B8*VLOOKUP(A8,$G$3:$T$11,5,FALSE)			Argentina	ARG	ARS	155	24.052%	-	-	142,524	1,449	(47,368)	(482)	-	-	-
9	Belgium	$259,826.39	42,480.56	=B9*VLOOKUP(A9,$G$3:$T$11,5,FALSE)			Belgium	BEL	EUR	7	16.350%	-	-	29,420	34,530	1,219	1,431	-	-	-
10	Denmark	$172,849.79	2,770.75	=B10*VLOOKUP(A10,$G$3:$T$11,5,FALSE)			Denmark	DNK	DKK	8	1.603%	-	-	4,149	655	(10,850)	(1,713)	-	20,049	20,049
11	Switzerland	$264,985.76	18,044.61	=B11*VLOOKUP(A11,$G$3:$T$11,5,FALSE)			Switzerland	CHE	CHF	9	6.810%	-	-	8,152	8,754	(4,958)	(5,324)	-	36,948	36,948
Sheet1

Cell Formulas
Range	Formula
C3:C5	C3	=IF(B3>$M$3,((B3*$K$3)+($M$3*$L$3)),(B3*($K$3+$L$3)))
D3:D11	D3	=FORMULATEXT(C3)
C6:C11	C6	=B6*VLOOKUP(A6,$G$3:$T$11,5,FALSE)

 

[Joe4]

You can nest them inside and IF formula, i.e.
=IF(OR(A3="Canada",A3="United States of America"),formula1,formula2)
where "formula1" is your formula for US and Canada and "formula2" is your other formula.

 

[etaf]

how about

=IF( OR ( A3 = "united states of america", A3 = "canada") , formula for US & Canada , formula for others )

 

[zinah]

You can nest them inside and IF formula, i.e.
=IF(OR(A3="Canada",A3="United States of America"),formula1,formula2)
where "formula1" is your formula for US and Canada and "formula2" is your other formula.

Thank you so much, the formula worked perfectly great!

 

[zinah]

how about

=IF( OR ( A3 = "united states of america", A3 = "canada") , formula for US & Canada , formula for others )

Thank you so much, the formula worked great

 

[Joe4]

You are welcome.
I am glad we were able to help!