I need to extract the sales tax from total sales for two different Sales tax rates . Is there a good "IF" formula for this?

Tom Donnelly 20 Reputation points
2026-10-30T00:37:16.28+00:00

I have two counties with different tax rates. Is there a good "IF" formula to extract the sales tax from the total. My first thought was to put the tax rates in separate cells i.e. 0.1055 in F4 and 0.095 in F5. Then have a number 1 or 2 or something similar to have the formula calculate the sales tax using the proper rate.

My thoughts were if the rate was 1 then extract sales tax from total using $F$4 and if 2 then extract sales tax using $F$5

Microsoft 365 and Office | Excel | For home | Windows
Answer accepted by question author

AI answer

2026-10-30T00:39:02.7433333+00:00

If the total already includes sales tax, select the applicable rate with IF and calculate the tax portion as Total × Rate ÷ (1 + Rate).

Assume:

  • A2 = total sale including tax
  • B2 = rate selector (1 or 2)
  • F4 = 0.1055 (10.55%)
  • F5 = 0.095 (9.5%)

Enter this formula in the sales-tax cell:

=IF(B2=1,A2*$F$4/(1+$F$4),IF(B2=2,A2*$F$5/(1+$F$5),""))

Excel’s IF function returns one calculation when a condition is true and another when it is false.^1^ The division by 1 + rate is necessary because the tax is already included in the total; this follows the same method as extracting 5% tax with Gross/105*5.^2^

For example, if A2 is $110.55 and B2 is 1, the formula returns $10.55 of included tax.

To calculate the sale before tax:

=A2-IF(B2=1,A2*$F$4/(1+$F$4),IF(B2=2,A2*$F$5/(1+$F$5),""))

If A2 is instead the pre-tax amount, use multiplication rather than extraction:

=IF(B2=1,A2*$F$4,IF(B2=2,A2*$F$5,""))

Format the result cell as Currency. Keep the dollar signs in $F$4 and $F$5 so those references remain fixed when the formula is filled down.

References

  1. IF function | Microsoft Support
  2. Struggling with sales tax formula - Microsoft Q&A
AI-generated content may be incorrect. Read our transparency notes for more information.

Was this answer helpful?

1 person found this answer helpful.

0 additional answers

Sort by: Newest
Most helpful Newest Oldest

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.