Practice free

AND function

AND checks whether all of your conditions are true at once, returning TRUE only if every one is, otherwise FALSE. It is mostly used inside IF to require several criteria together.

Example

Read it as a sentence: is E5 at least 10, and is I5 FALSE? Both are, so the answer is TRUE.

Practice this in the app

How AND works

AND asks one question about several tests: are all of them true? Is the client active and over 18? Is the order paid and shipped? Is the reading inside the safe band?

The answer is always TRUE or FALSE. One failed test is enough to make the whole thing FALSE, however many others pass.

In the example above, the cigar in row 5 is worth 10, which is at least 10, and its Insured cell is FALSE. Both tests pass, so AND returns TRUE. Run the same tests on the magnifying glass in row 12 and the first passes, but the magnifying glass is insured, so the answer is FALSE.

On its own AND only reports TRUE or FALSE. Most of the time it sits inside IF, which turns that answer into a word, a number or another formula.

Syntax

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

Arguments in [square brackets] are optional.

The first test logical1 (required)
First condition or value.
More tests logical2 (optional)
More conditions, up to 255.

Returns: TRUE if every condition is TRUE, otherwise FALSE.

More examples

Each picture shows only the columns that formula reads.

  1. Use AND inside IF

    Is this person an inspector who is also active?

    AND makes the test and IF picks the words. G. Lestrade in row 6 is an Inspector and his Active cell is TRUE, so IF shows On call. The second formula uses the cigar's two tests to flag it with Insure now.

    • =IF(AND(Items!E5>=10,Items!I5=FALSE),"Insure now","OK") Insure now
  2. Check a value is between two numbers

    Is Stanley Hopkins between 18 and 40?

    A range check is two tests on the same cell: at least the low end, and no more than the high end. His age in E8 is 30, so both hold and AND returns TRUE.

  3. Test three things at once

    Is Scotland Yard in London, under 5 miles away and visited more than 10 times?

    Add as many tests as you need, separated by commas. Row 3 of Places says London, 2.8 miles and 41 visits, so all three pass.

  4. Check a date falls in a year

    Were the boxer cartridges bought in 2024?

    Compare the date with the first day of the year and the first day of the next one. H2 holds November 2, 2024, which is on or after January 1, 2024 and before January 1, 2025.

  5. Check every cell in a range

    Is there at least one of every item in stock?

    Items!D2:D7>0 tests all six quantities, and AND returns TRUE only if every one passes. They all do, so the answer is TRUE.

AND or OR?

AND needs every test to pass. OR needs only one. The same cigar shows the difference: it is not in the Chemistry category, but it is worth 10, so an IF with OR on those two tests sends it to the Locked cabinet. With AND it would need both.

Use AND for rules where everything must be true, such as active and an inspector. Use OR for rules where any one reason is enough, such as fragile or valuable.

Watch out for

AND over ranges gives one answer, not one per row

AND(Items!D2:D6>10,Items!E2:E6<5) does not check each row. It folds all ten tests into a single FALSE. To test row by row, multiply the tests instead: (D2:D6>10)*(E2:E6<5) spills 1 or 0 for each of the five rows.

  • =AND(Items!D2:D6>10,Items!E2:E6<5) FALSE
  • =(Items!D2:D6>10)*(Items!E2:E6<5) 1

Text that looks like a number compares as text

Data!F2 shows 123, but it was entered as text. In a comparison Excel counts text as larger than any number, so F2>5 is TRUE whatever the digits say. Convert the cell to a number first.

  • =AND(Data!F2>5) TRUE

Capitals don't count in text tests

Items!C3="smoking" is TRUE for a cell that says Smoking. When AND compares words, capital and small letters are the same to it.

  • =AND(Items!C3="smoking",Items!D3>5) TRUE

Blank and text cells are skipped, but not always

When AND reads a cell that holds text, or nothing, it leaves that cell out and judges the rest. A blank cell on its own leaves nothing to judge, so AND returns #VALUE!. A word typed straight into AND, such as AND("a"), gives #VALUE! too.

  • =AND(Data!F4,Items!B2) TRUE
  • =AND(Data!F1,TRUE) TRUE
  • =AND(Data!F1) #VALUE!
  • =AND("a") #VALUE!

One error makes the whole answer an error

When a test reads a cell holding #DIV/0!, AND returns #DIV/0! too, even when another test is TRUE. Fix the error first, or wrap the source in IFERROR.

  • =AND(Data!F5,TRUE) #DIV/0!

Questions people ask

What is the AND formula in Excel?

=AND(test1, test2, …) returns TRUE when every test is true and FALSE when any one fails. The cigar passes both tests; the magnifying glass fails one.

  • =AND(Items!E5>=10,Items!I5=FALSE) TRUE
  • =AND(Items!E12>=10,Items!I12=FALSE) FALSE
How do I check a cell is not empty with AND?

Test it with <>"", which means not equal to empty. James Moriarty has no Home on file and is not active, so both tests fail and AND returns FALSE.

  • =AND(People!D10<>"",People!H10) FALSE
What does $ mean in an Excel formula?

It locks the column or row so the reference stays put when you copy the formula. $D$2 in a running total always starts from D2.

  • =SUM(Items!$D$2:D4) 39
How do I use & in a formula?

The ampersand joins text; it doesn't test anything. Places!B3&", "&Places!D3 returns Scotland Yard, London. For a this-and-that test, use AND.

  • =Places!B3&", "&Places!D3 Scotland Yard, London

Where it works

Excel
AND is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use.

Part of Logical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .