AND
Every test has to hold.
Check that every one of several conditions holds
TRUE when every test is TRUE, FALSE as soon as one is not
Syntax
=AND(test1, [test2], ...)
| Argument | Required | What goes here |
|---|---|---|
test1 | Required | A comparison that must be true. |
test2 | Optional | A comparison that must be true. |
How to use AND
AND takes as many tests as you want to give it and returns TRUE only if all of them hold.
On its own it is rarely the finished answer. It goes in the test slot of an IF, or into a conditional formatting rule, where what you actually want is the action that follows.
Widening a rule is what AND is for: a chase list of everything overdue is too long to be useful, and everything overdue by more than thirty days and worth more than five hundred pounds is a morning's work.
Swapping AND for OR keeps the same two tests and asks a different question, so it is worth saying out loud which one you mean before writing it.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Supplier | Status | Days overdue | Amount | Supplier | Terms (days) | |
| 2 | INV-8801 | Brightwell Paper | Overdue | 12 | 940 | Ashgrove Catering | 30 | |
| 3 | INV-8802 | Calder Utilities | Queried | 47 | 1180 | Brightwell Paper | 30 | |
| 4 | INV-8803 | Meridian Freight | Overdue | 63 | 2450 | Calder Utilities | 14 | |
| 5 | INV-8804 | Ashgrove Catering | On terms | 0 | 405 | Meridian Freight | 45 | |
| 6 | INV-8805 | Brightwell Paper | Overdue | 38 | 760 | Thornbury Print | 60 | |
| 7 | INV-8806 | Thornbury Print | Overdue | 91 | 1320 | |||
| 8 | INV-8807 | Meridian Freight | On terms | 0 | 580 | |||
| 9 | INV-8808 | Calder Utilities | Overdue | 24 | 215 |
| Formula | Result |
|---|---|
=AND(D2>30,E2>500)The amount clears the threshold and the age does not, so the answer is FALSE. AND needs both. | FALSE |
=AND(D4>30,E4>500)Sixty-three days and two thousand four hundred and fifty pounds. Both hold. | TRUE |
=IF(AND(D4>30,E4>500),"Chase","Leave")Wrapped in IF, which is how AND is nearly always used. | Chase |
=IF(AND(OR(C3="Overdue",C3="Queried"),E3>1000),"Escalate","")OR nested inside AND: either of two statuses, and over a thousand pounds. | Escalate |
Notes
- AND takes up to 255 tests, though a rule needing more than three is usually a sign the question has not been settled yet.
- Text comparisons ignore capitals, so C2="overdue" and C2="Overdue" behave the same.
Related
A reference tells you what it does. A unit makes you use it.