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.
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.
-
Use AND inside IF
Is this person an inspector who is also active?
Excel shows On callCH1RoleActive2DetectiveTRUE3DoctorTRUE4LandladyTRUE5Civil servantTRUE6InspectorTRUEPeopleAND 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
-
-
Check a value is between two numbers
Is Stanley Hopkins between 18 and 40?
Excel shows TRUEE1Age241343458548646744830PeopleA 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.
-
Test three things at once
Is Scotland Yard in London, under 5 miles away and visited more than 10 times?
Excel shows TRUEDEG1RegionMilesVisits2London03653London2.841PlacesAdd 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.
-
Check a date falls in a year
Were the boxer cartridges bought in 2024?
Excel shows TRUEH1Acquired211/2/2024ItemsCompare 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.
-
Check every cell in a range
Is there at least one of every item in stock?
Excel shows TRUED1Qty212374205561579ItemsItems!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!D3Scotland 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 .