Forum Discussion

Copper Contributor

Mar 08, 2022

Solved

IFS formula returns #NAME? error

Hi everyone. I'm new to this forum I am Canadian, and I am working on an invoice log. I created a dropdown list in my spreadsheet (column D) with all Canadian provinces, and now I would like column ...

excel

Formulas and Functions

HansVogelaar
Mar 08, 2022
Anndie

You have used so-called curly quotes ” around AB etc.

Excel expects straight quotes "

=IFS(D2="AB",0.0005*100,D2="BC",0.0005*100,D2="MB",0.0005*100,D2="NB",0.0015*100,D2="NL",0.0015*100,D2="NT",0.0005*100,D2="NS",0.0015*100,D2="NU",0.0005*100,D2="ON",0.0013*100,D2="PEI",0.0015*100,D2="QC",0.0005*100,D2="SK",0.0005*100,D2="YT",0.0005*100)

A shorter version:

=IFS(OR(D2={"AB","MB","BC","NT","NU","QC","SK","YT"}),0.0005,OR(D2={"NB","NL","NS","PEI"}),0.0015,D2="ON",0.0013)*100

HansVogelaar

MVP

Mar 08, 2022

Anndie

You have used so-called curly quotes ” around AB etc.

Excel expects straight quotes "

=IFS(D2="AB",0.0005*100,D2="BC",0.0005*100,D2="MB",0.0005*100,D2="NB",0.0015*100,D2="NL",0.0015*100,D2="NT",0.0005*100,D2="NS",0.0015*100,D2="NU",0.0005*100,D2="ON",0.0013*100,D2="PEI",0.0015*100,D2="QC",0.0005*100,D2="SK",0.0005*100,D2="YT",0.0005*100)

A shorter version:

=IFS(OR(D2={"AB","MB","BC","NT","NU","QC","SK","YT"}),0.0005,OR(D2={"NB","NL","NS","PEI"}),0.0015,D2="ON",0.0013)*100

Anndie

Copper Contributor

Mar 08, 2022

I'm sorry to be a pest, but I have one more quick question. Now I would like my sheet to calculate the actual tax amount, depending on the province, in column H using the net total in column E. How can I do that?

OliverScheurich

Gold Contributor

Mar 08, 2022

Anndie

=LET(province,UNIQUE(H2:H48),percentage, IFS(province="AB",0.0005*100,province="BC",0.0005*100,province="MB",0.0005*100,province="NB",0.0015*100,province="NL",0.0015*100,province="NT",0.0005*100,province="NS",0.0015*100,province="NU",0.0005*100,province="ON",0.0013*100,province="PEI",0.0015*100,province="QC",0.0005*100,province="SK",0.0005*100,province="YT",0.0005*100),netvalue,SUMIF(H2:H48,province,E2:E48),taxamount,netvalue*percentage,result,SORT(CHOOSE({1,2,3,4},province,netvalue,percentage,taxamount),1),result)

Maybe you want apply above formula to return province and net amount along with the tax percentage and tax amount.

provinces.pdf162 KB

HansVogelaar
MVP
Mar 08, 2022
Anndie

Depending on how it is set up, perhaps

=IFS(OR(D2={"AB","MB","BC","NT","NU","QC","SK","YT"}),0.0005,OR(D2={"NB","NL","NS","PEI"}),0.0015,D2="ON",0.0013)*E2

or

=IFS(OR(D2={"AB","MB","BC","NT","NU","QC","SK","YT"}),0.0005,OR(D2={"NB","NL","NS","PEI"}),0.0015,D2="ON",0.0013)*100*E2
- Anndie
  Copper Contributor
  Mar 08, 2022
  That worked perfect! Thank you so much for your help! But I have one last question and I promise not to pester you again...lol. How do I add "subtract the entire formula from the amount in column G"?
  - HansVogelaar
    MVP
    Mar 08, 2022
    Anndie
    
    How about
    
    =G2-IFS(OR(D2={"AB","MB","BC","NT","NU","QC","SK","YT"}),0.0005,OR(D2={"NB","NL","NS","PEI"}),0.0015,D2="ON",0.0013)*E2
    
    or something like that?