Skip to main content

Excel AND function

In Excel, the AND function tests one or more cells with separate conditions, if the conditions are all met, then TRUE value is returned; if any of the conditions is evaluate false, a FALSE value will be displayed. It is often used with other Excel functions.

doc and function 1


 Syntax:

The syntax for the AND function in Excel is:

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

 Arguments:

  • Logical1: Required. The first condition or logical value that you want to test.
  • Logical2: Optional. The second condition or logical value to evaluate.

Notes:

  • 1. In Excel 2007 and later versions, you can enter up to 255 conditions, in Excel 2003, only handle up to 30 conditions.
  • 2. The AND function will return #Value! Error if any of the logical conditions are text values.
  • doc and function 9

 Return:

Test multiple conditions, and returns TRUE if all arguments are met, otherwise, FALSE is returned.


 Examples:

Example 1: Use AND function only

Supposing, there are three columns which contain products, orders and salesman, now, you want to check which rows meet these conditions: Product = KTE, Order > 100, Salesman = Nicol.

Please apply the following formula into a blank cell where you want to put the result, and then drag the fill handle down the cell to apply the formula, and you will get the following result:

=AND(A2="KTE", B2>100,C2="Nicol")

doc and function 2


Example 2: Use IF and AND functions together

It is a common usage for us to combine the IF and AND functions together.

For example, you have a table contains the students’ test scores, both of the first and the second subject scores must be equal to or greater than 60, then the student passes the final exam, otherwise, fail the exam. See screenshot:

doc and function 3

To check if the student passes the exam or not, using the IF and AND functions together to solve it, please use this formula:

=IF((AND(B2>=60, C2>=60)), "Pass", "Fail")

This formula indicates that, it will return Pass, if a value in column B >=60 and a value in column C >=60. Otherwise, the formula returns Fail. See screenshot:

doc and function 4

Click for knowing more about IF function…


Example 3: Use IF and AND functions together for calculation

The IF AND functions can also perform calculations based on whether AND function is TRUE or FALSE.

If you want to calculate the bonus or commission for your company staff based on their sales performance, for instance, if the person’s sales equals or greater than $50000, and his performance is A, then he will get 3% bonus, otherwise, he will no bonus. How to calculate the bonus in Excel quickly and easily?

doc and function 5

To calculate the bonus, please apply this formula:

=IF(AND(B3>=50000,C3="A"),B3*3%,0)

The above formula indicates, IF Sales are equal or greater than 50000, AND Performance is A grade, then multiply Sales by 3%, otherwise it will return 0.

doc and function 6

And then, you will get the following result as you need:

doc and function 7

Tip: If you want to calculate the bonus for the sales which are less than 50000 instead of returning 0, for example, multiply Sales by 1.5%, when Sales less than 50000, you should use this formula:

=IF(AND(B3>=50000,C3="A"),B3*3%,B3*1.5%)

doc and function 8

Click for knowing more about IF function…


The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description


Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations