Support Cart
Search
Office support
Products Devices
Excel
What's / Formulas and functions / Logical / AND function
new
Install Office
AND function
Account & billing
Templates
More support
Excel for Microsoft 365, More...
Use the AND function, one of the logical functions, to determine if all conditions in a test
are TRUE.
Example
Technical Details
Examples
Here are some general examples of using AND by itself, and in conjunction with the IF
function.
Formula Description
=AND(A2>1,A2<100) Displays TRUE if A2 is greater than 1 AND less than
100, otherwise it displays FALSE.
=IF(AND(A2<A3,A2<100),A2,"The Displays the value in cell A2 if it’s less than A3 AND
value is out of range") less than 100, otherwise it displays the message
"The value is out of range".
=IF(AND(A3>1,A3<100),A3,"The Displays the value in cell A3 if it is greater than 1
value is out of range") AND less than 100, otherwise it displays a message.
You can substitute any message of your choice.
Bonus Calculation
Here is a fairly common scenario where we need to calculate if sales people qualify for a
bonus using IF and AND.
=IF(AND(B14>=$B$7,C14>=$B$5),B14*$B$8,0) – IF Total Sales are greater than or equal
(>=) to the Sales Goal, AND Accounts are greater than or equal to (>=) the Account Goal,
then multiply Total Sales by the Bonus %, otherwise return 0.
Need more help?
You can always ask an expert in the Excel Tech Community, get support in the Answers
community, or suggest a new feature or improvement on Excel User Voice.
Related Topics
Video: Advanced IF functions
Learn how to use nested functions in a formula
IF function
OR function
NOT function
Overview of formulas in Excel
How to avoid broken formulas
Detect errors in formulas
Keyboard shortcuts in Excel
Logical functions (reference)
Excel functions (alphabetical)
Excel functions (by category)
Expand your Office skills
EXPLORE TRAINING
Get instant Excel help
CONNECT TO AN EXPERT NOW
Subject to Got It terms and conditions
Was this information helpful? Yes No
What's new Microsoft Education Enterprise Developer Company
Store
Surface Duo Microsoft in Azure Microsoft Visual Careers
Account profile education Studio
Surface Go 2 AppSource About Microsoft
Download Office for Windows Dev
Surface Book 3 Center students Automotive Center Company news
Microsoft 365 Microsoft Store Office 365 for Government Developer Privacy at
support schools Center Microsoft
Surface Pro X Healthcare
Returns Deals for Microsoft Investors
Windows 10 apps students & Manufacturing developer
Order tracking parents program Diversity and
Financial services inclusion
Virtual Microsoft Azure Channel 9
workshops and in education Retail Accessibility
training Office Dev
Center Security
Microsoft Store
Promise Microsoft
Garage
English (United States)
Sitemap Contact Microsoft Privacy & cookies Terms of use Trademarks Safety & eco
About our ads © Microsoft 2020