# How to calculate shipping cost with an IF formula in Excel

Return 0 when the order reaches the free-shipping threshold and the fee otherwise: =IF(B2>=$F$1,0,$F$2).

**Excel:** `=IF(B2>=$F$1,0,$F$2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Order | Subtotal | Shipping |  | Free from | 75 |
| 1001 | 82.4 |  |  | Flat fee | 6.95 |
| 1002 | 34.99 |  |  |  |  |
| 1003 | 75 |  |  |  |  |
| 1004 | 120 |  |  |  |  |
| 1005 | 19.5 |  |  |  |  |

Formula in C2, filled to C6: `=IF(B2>=$F$1,0,$F$2)`

Result: $0.00; $6.95; $0.00; $0.00; $6.95

## How it works

When the subtotal reaches the threshold in `F1`, IF returns 0; otherwise it returns the fee in `F2`. Order 1003 is exactly 75 and ships free because the test uses >=.

## Other ways

- `=(B2<$F$1)*$F$2`: The test is 1 or 0, so it can multiply the fee directly.
- `=IFS(B2>=$F$1,0,B2>=30,4.95,TRUE,$F$2)`: Adds a middle tier with reduced shipping from 30. (Order 1002 pays 4.95.)

## Common mistakes

- > versus >= decides the order exactly at the threshold. Match your policy's wording: "over" or "from".
- Decide whether the threshold applies before or after discounts and tax, and calculate on that amount.

## Questions

**How do I show the total with shipping?**

`=B2+IF(B2>=$F$1,0,$F$2)`.

**How do I tell customers how much more to spend?**

`=IF(B2<$F$1,"Add "&TEXT($F$1-B2,"$0.00")&" for free shipping","")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/shipping-cost-with-if