Check two conditions at once with IF and AND

Sample data. Orders in A:C, the formula in D2 is copied down.

The formula

Click the first Shipping cell (D2) and type:

=IF(AND(B2>=50,C2="CA"),"Free","Paid")

D2 shows Free: order 1001 is 72.00 and ships to CA. Copy it down: 1002 is in Canada but only 35.00, so it is Paid; 1003 is 88.00 but ships to the US, so it is Paid too.

• AND(B2>=50,C2="CA") is TRUE only when both tests pass.
• "Free" is what IF shows when AND is TRUE.
• "Paid" is what it shows otherwise.

Text goes in straight double quotes; numbers like 50 do not.

Either rule is enough: use OR

Swap AND for OR and the row passes when any one test is true:

=IF(OR(B2>=100,C2="CA"),"Free","Paid")

Now every Canadian order is Free, and so is order 1006 (120.00 to the US). Only 1003 and 1005 stay Paid.

Three outcomes: put an IF inside the IF

Return a shipping cost instead of a word, with a different rate for Canada and everywhere else:

=IF(AND(B2>=50,C2="CA"),0,IF(C2="CA",5,15))

Free for Canadian orders of 50 or more, 5 for smaller Canadian orders, 15 for the rest. Down the sample the costs are 0, 5, 15, 0, 15, 15, and =SUM(F2:F7) gives 50 in shipping.

Count the results

Once the column is filled, COUNTIF tells you how many orders ship free:

=COUNTIF(D2:D7,"Free")

The sample has 2.

The mistake everyone makes once

Writing the rule the way you say it, B2>=50 AND C2="CA" inside the IF, is not a valid formula in Excel or Google Sheets. AND goes first, with the tests inside its brackets, separated by commas. To see why a row came out the way it did, put the AND part in a cell by itself:

=AND(B3>=50,C3="CA")

Order 1002 shows FALSE, because 35.00 fails the first test. IF, AND and OR work in every version of Excel and in Google Sheets.

Watch it in 35 seconds

Templates with the math already done

Rather skip the formulas? These templates come with every formula built and checked, plus a filled-in sample.