Conditional IF based on country

zinah

Active Member
Joined
Nov 28, 2018
Messages
368
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRST
2CountryAmountCalcUS and Canada is a differnet formula, rest of the countries is the sameEmployee CountryCountry CodeCurrency CodeCompany%1%2Field1Field2Field3Field4Field5Field6Field7Field8
3United States of America319,255.9014,488.14=IF(B3>$M$3,((B3*$K$3)+($M$3*$L$3)),(B3*($K$3+$L$3)))United States of AmericaUSAUSD11.683%6.200%147,00018,24318,243--6,500--
4United States of America257,600.4313,450.27=IF(B4>$M$3,((B4*$K$3)+($M$3*$L$3)),(B4*($K$3+$L$3)))United States of AmericaUSAUSD21.683%6.200%147,00018,24318,243--6,500--
5Canada210,000.7312,649.01=IF(B5>$M$3,((B5*$K$3)+($M$3*$L$3)),(B5*($K$3+$L$3)))CanadaCANCAD31.950%7.662%61,9006,3514,9793,7312,925-4,0354,035
6Chile$60,556.61-=B6*VLOOKUP(A6,$G$3:$T$11,5,FALSE)ChileCHLCLP4---########5,065########2,401-788788
7Guatemala$238,608.2925,841.83=B7*VLOOKUP(A7,$G$3:$T$11,5,FALSE)GuatemalaGTMGTQ510.830%--50,9526,58947,9536,201---
8Argentina93674.9922,530.54=B8*VLOOKUP(A8,$G$3:$T$11,5,FALSE)ArgentinaARGARS15524.052%--142,5241,449(47,368)(482)---
9Belgium$259,826.3942,480.56=B9*VLOOKUP(A9,$G$3:$T$11,5,FALSE)BelgiumBELEUR716.350%--29,42034,5301,2191,431---
10Denmark$172,849.792,770.75=B10*VLOOKUP(A10,$G$3:$T$11,5,FALSE)DenmarkDNKDKK81.603%--4,149655(10,850)(1,713)-20,04920,049
11Switzerland$264,985.7618,044.61=B11*VLOOKUP(A11,$G$3:$T$11,5,FALSE)SwitzerlandCHECHF96.810%--8,1528,754(4,958)(5,324)-36,94836,948
Sheet1
Cell Formulas
RangeFormula
C3:C5C3=IF(B3>$M$3,((B3*$K$3)+($M$3*$L$3)),(B3*($K$3+$L$3)))
D3:D11D3=FORMULATEXT(C3)
C6:C11C6=B6*VLOOKUP(A6,$G$3:$T$11,5,FALSE)
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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.
 
Upvote 0
Solution
how about

=IF( OR ( A3 = "united states of america", A3 = "canada") , formula for US & Canada , formula for others )
 
Upvote 0
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!
 
Upvote 0
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 :)
 
Upvote 0
You are welcome.
I am glad we were able to help!
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top