IFS formula returns #NAME? error

[Anndie]

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 G to show the sales tax rate depending on the province. For example, if column D says ON I would like column G to automatically show 13%.

Yesterday I spent all day looking for tutorials online, but so far, nothing has worked. The formula I'm using right now is:

=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))

But I get a #NAME? error in the G column, and I don't know  what I'm doing wrong. I am using Excel 365. Thanks in advance for all your help.

[#2]

@Anndie 

 =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))

Does this work in your sheet? I replaced ” with " and now it works in my sheet.

[#3 · Accepted solution]

@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

[#4]

Yes, it did! Thank you so much!

[#5]

Amazing! Thank you so much for the abbreviated version! It worked like a charm!

[#6]

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?

[#7]

@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

[#8]

@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.

[#9]

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"?

[#10]

@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?

[#11]

@Hans Vogelaar It's not working...it's strange. I have a separate sheet where I create my invoices, and the amounts don't match up.

I checked the formula that I use on those to calculate the totals automatically and they seem right. In my invoice workbook I have the subtotal, minus a discount (in some cases), plus tax (see example in image 1), but the result I get using the formula you so kindly helped me with is what is shown in the second image. As you can see, the tax in the sheet that I am working on right now is marked in red because it does not match the amount of the actual invoice, so I thought that it could be because in the invoice, I subtracted the discount before adding the tax, but it does not work with the formula you just sent me. Also, column P (with no title) is the amount of the invoice using the formula vs. the amount in column K, which is what I actually sent. 

[#12]

@Anndie 

Could you attach a small sample workbook, or if that is not possible, could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

[#13]

Sure thing!
Here's the link

https://1drv.ms/x/s!Aiqn_JB4mXBdkqJeu6kJi_tQDRz7Hw?e=iuPauZ

The problem seems to be with the amounts that have a discount. The first sheet has the sample of the document I am currently working on, and the second sheet has the calculations that I made for the invoices I sent out. On the first, I highlighted the entries with a problem, but left the ones with no discount for reference.

I can't thank you enough for your help!

[#14]

@Anndie 

Thank you! The problem is that we calculated the tax over the full invoice amount instead of over the discounted amount. The formula in H2 should be =G2*(C2-E2)

I also simplified the formulas in columns E and F slightly.

See the attached version.

[#15]

Amazing! I really can't thank you enough for all your help! Have a wonderful day and stay safe!

[#16]

office 2021 How to fix ifs function NAME? error 

=IFS(X4=9,K4*2,X4=8,K4*2,X4=7,K4*3,X4=6,K4*4,X4=5,K4*30,X4=4,K4*70,X4=3,K4*5000,X4=2,K4*20000,X4=1,K4*200000)

[#17]

@turuu223 

Do you use the R1C1 reference system?

[#18]

@Anndie 

Since this question seems to have come round again, one could also use SWITCH.

= SWITCH(province,"NB",15%,"NL",15%,"NS",15%,"PEI",15%,"ON",13%,5%)

Including other derived quantities
= LET(
    taxRate,  SWITCH(province,"NB",15%,"NL",15%,"NS",15%,"PEI",15%,"ON",13%,5%),
    tax,      taxRate*netAmt,
    grossAmt, netAmt+tax,
    HSTACK(taxRate, tax, grossAmt)
  )

Note: The final 5% is the default value taken if no other match is found.

[#19]

Hi
I think an easier approach would be to have a separate sales tax sheet with two columns one for state and the other for tax rate and just use xlookup.
Cheers#Paul

[#20]

@Hans Vogelaar no i don't now R1C1 reference