IF with AND in Excel: Check Two Conditions at Once

An IF and formula in Excel checks two or more conditions at once and returns one result only when all of them are true. You put AND inside IF: =IF(AND(test1, test2), "Yes", "No"). AND does the checking and IF chooses what to show. This guide builds the formula on a bonus table, then covers number ranges, text, dates, mixing AND with OR, and the mistakes that cause wrong results.

How AND works on its own#

AND takes a list of conditions and returns TRUE only if every one is true. If even one is false, AND returns FALSE.

=AND(logical1, [logical2], ...)

On its own, AND only shows TRUE or FALSE. Wrapping it in IF lets you show words, numbers or calculations instead. The lesson on AND, OR and NOT practises these building blocks, and the AND function guide is a quick reference.

How to write IF with AND, step by step#

The examples use this table in A1:C6. A bonus needs sales of at least 80,000 and attendance of at least 95%:

1. Click D2 and type =IF(AND(.
2. Type the first condition, B2>=80000, then a comma.
3. Type the second condition, C2>=95%, and close the AND bracket.
4. Add the two results, "Bonus","No bonus", and close the IF bracket.

=IF(AND(B2>=80000,C2>=95%),"Bonus","No bonus")

Fill down to D6 (Windows: Ctrl+D; Mac: ⌘D). Aiko and Dmitri get Bonus. Bruno has the sales but not the attendance, Carmen has the attendance but not the sales, and Esther misses both, so they show No bonus.

Dmitri's attendance is exactly 95%, and >= includes it. With > he would miss out. The guide to IF greater than explains that difference.

How to check if a number is between two values#

A range check is the most common use of AND: the value must be above a lower limit and below an upper limit.

=IF(AND(B2>=60000,B2<=90000),"Mid band","Outside")

Aiko (85,000) and Esther (78,000) are in the mid band. Write each comparison in full. The maths-style 60000<=B2<=90000 doesn't work in Excel: it compares the first part, gets TRUE or FALSE, then compares that with 90000, and returns the wrong answer without any error. See IF between two numbers for more range examples.

How to combine text and number conditions#

AND can mix any kinds of conditions, as long as each one returns TRUE or FALSE. Text goes in double quotes.

=IF(AND(B2="North",C2>=500),"Priority","Standard")

This flags orders that are from the North region and worth 500 or more. Text comparisons aren't case-sensitive, so north and NORTH also match. For partial text matches, such as "contains North", see IF with text.

To make sure a cell isn't empty before you test it, add a not-blank condition: =IF(AND(B2<>"",C2>=500),"Priority",""). The guide to IF cell is blank covers blank checks in detail.

How to mix AND with OR#

Use OR inside AND when some conditions are alternatives. Say a bonus needs sales of at least 80,000 and a grade of either Gold or Silver in column E:

=IF(AND(B2>=80000,OR(E2="Gold",E2="Silver")),"Bonus","No bonus")

Read it from the inside out: OR is TRUE if the grade is Gold or Silver; AND is TRUE if that and the sales test are both true. Brackets keep each part separate. For more on OR, read IF with OR.

IF AND with more than two outcomes#

For tiers, nest IFs, putting the strictest test first. Each test can have its own AND:

=IF(AND(B2>=100000,C2>=95%),"Gold",IF(AND(B2>=80000,C2>=90%),"Silver","None"))

Dmitri (110,000 and 95%) gets Gold. Aiko (85,000 and 97%) gets Silver. Bruno has the sales for Silver but 88% attendance, so he gets None. In Excel 2019 and later, IFS does the same with less nesting: =IFS(AND(B2>=100000,C2>=95%),"Gold",AND(B2>=80000,C2>=90%),"Silver",TRUE,"None").

AND beyond IF: counting and highlighting#

AND isn't only for IF. Two related tasks come up often in reports and tests.

Counting rows that meet both conditions. You don't need a helper column of IF formulas. COUNTIFS checks several conditions directly:

=COUNTIFS(B2:B6,">=80000",C2:C6,">=0.95")

This returns 2 (Aiko and Dmitri). SUMIFS and AVERAGEIFS work the same way for totals and averages.

Highlighting rows. In conditional formatting, choose "Use a formula to determine which cells to format" and enter the AND test on its own, without IF: =AND($B2>=80000,$C2>=95%). Every row where it returns TRUE gets the colour. The $ before the column letters keeps each row checking columns B and C.

You may also see =IF((B2>=80000)*(C2>=95%),"Bonus","No bonus"). Multiplying TRUE and FALSE values gives 1 only when both are TRUE, so it acts like AND. It works, but AND is easier to read.

Common IF AND mistakes#

Check these when the results look wrong:

1. Chained comparisons. 10<=B2<=20 gives wrong answers silently. Use AND(B2>=10,B2<=20).
2. Brackets in the wrong place. Close AND before the true and false results: IF(AND(a,b),"Yes","No").
3. Percent as a whole number. If attendance is stored as 0.95, test C2>=95% or C2>=0.95, not C2>=95.
4. Text passed straight to AND. AND(C2,...) where C2 holds "Yes" returns #VALUE!. Compare it: C2="Yes".
5. Using AND when you mean OR. If any one condition should be enough, you need OR.

The IF function guide and the article on the IF function explained simply cover the IF side in more depth.

Practise it#

IF with AND is one of the most tested formulas in Excel job assessments, because it checks logic and syntax together. Practise AND, OR and NOT in a live spreadsheet: you type the formula and it checks it instantly. Then practise the IF function on its own, and try the intermediate Excel test when you're ready.