EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Logical Functions in Excel Tutorial AND Function in Excel
Secondary Sidebar
Excel Functions
  • Logical Functions in Excel
    • Excel IF AND Function
    • IFERROR Formula in Excel
    • SUMIF with Multiple Criteria
    • Excel SUMIFS with Dates
    • FALSE Function in Excel
    • Excel IF Function
    • Excel Logical Test
    • Excel XOR Function
    • Logical Functions Excel
    • IFERROR Excel Function
    • TRUE Function in Excel
    • OR Function in Excel
    • NOT in Excel
    • AND Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Lookup and Reference Functions in Excel (36+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (220+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL ADVANCED COURSE
  • Excel Data Analysis Certification
  • Excel for Marketing Certification

AND Function in Excel

By Pradeep SPradeep S

AND Function in Excel

AND Function in Excel (Table of Contents)

  • AND Function in Excel
  • AND Formula in Excel
  • How to use AND Function in Excel?

Excel AND Function

AND function is a logical function that is used when we need to carry to values or fields at the same time. AND Function returns the FALSE if any of the used logical values is FALSE. If we can to get the answer as TRUE, then all the logic used in AND function should be correct. Also, AND function works best when used with a combination of other conditional functions such as If, CountIf.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

It is the most commonly used logical function alone or in combination with other operators like:

  • “IF”
  • “OR”
  • “NOT”
  • < (less than)
  • > (greater than)
  • = (equal to)
  • <= (less than or equal to)
  • >= (greater than or equal to)
  • <> (not equal to)

It is most commonly used in conditional formatting.

AND function in Excel is used to test multiple conditions; up to 255 logical conditions can be used at the same time (Note: In Excel 2003 version, the function can only handle up to 30 arguments). AND function can operate on text, numbers, or cell references. AND function needs at least two arguments to return a value. A comma separates arguments; In the end, it provides either a ‘true’ or ‘false’ output based on the arguments inside the AND function.

AND Formula in Excel:

Below is the AND Formula in Excel.

AND Formula in Excel

logical1: logical value to evaluate, it’s a first condition to evaluate

logical2: logical value to evaluate, it’s a second condition to evaluate

How to Use the AND Function in Excel?

This AND function in excel is very simple easy to use. Let us now see how to use AND function in Excel with the help of some examples.

You can download this AND function in Excel Template here – AND function in Excel Template

Example #1

If we enter =AND (5>2, 6=6) in an excel cell, the answer or formula result would be TRUE. It’s true because 5 is greater than 2 and 6 does equal 6.

AND Example 1

Example #2

If we enter =AND (5>2, 6=8) in an excel cell, the answer or formula result would be FALSE. It’s false because 5 is greater than 2, but 6 is not greater than 8

and function in excel example 2

NOTE:

  • In AND function formula, each term inside the parentheses is separated by a comma like other functions in excel.
  • If an array or reference argument contains text or empty cells, those values are ignored.
  • Suppose the specified range contains no logical values AND returns the #VALUE! error value.

Example #3

In this example, if a student passes all the subject, then he has passed the exam

=AND (B6=”PASS”, C6=”PASS”, D6=”PASS”, E6=”PASS”)

The text strings stored in column B, C, D & E, must be “PASS”. Each term inside the parentheses needs to be separated by a comma like other function in excel; here, it returns the value TRUE if all of the arguments evaluate TRUE. i.e. if students have passed all the subjects, it returns the value TRUE,

and function in excel Example 3-1

otherwise, even if the students have failed in any of the one subjects, it returns the value FALSE

AND Example 3-2

Example #4

Using other operators (> (greater than) = (equal to) along with AND Function

AND function returns TRUE if first logical condition, Gender parameter I12 = MALE, and the second logical condition, age parameter should be equal to or more than 45 years, i.e. H12>=45. else it returns FALSE. Herewith reference to HARRY, it returns the value TRUE, 

AND Example 4-1

In the case of other candidates, SUMITH, it returns the value FALSE.

AND Example 4-2

Example #5

Using AND with “IF” Function with other operators

Suppose you have a table with the various subject score, need to find out the results of the exam whether they have passed the exam or not

The first score, stored in column B with the first subject, must be equal to or greater than 40. The second score, listed in column C with the second subject, must be equal to or greater than 40. Similarly, the third & fourth score, with third & fourth subject stored in column D & E, must be equal to or greater than 40. If all the above four conditions are met, a student passes the exam.

The easiest way to make a proper IF/AND formula is to initially write down the condition first and later add it in the logical test argument of your IF function:

Condition: AND(B18>=40, C18>=40, D18>=40, E18>=40)

IF/AND formula: =IF( AND(B18>=40, C18>=40, D18>=40, E18>=40), “PASS”, “FAIL”)

AND Example 5

Example #6

Using AND function with “OR” FUNCTION

Using IF and AND together is actually quite simple; in the below-mentioned example, irrespective of brand, in the table array, need to find out the sales of red color shoes which are more than 50 numbers by using AND function with “OR” function.

The first column contains the number of shoes sold; the second column contains the brand of shoes, the third column contains color of shoes, Here the condition is data in the I column should be equal to, or more than 50 & simultaneously the color of the shoes should be red. If both the above conditions are met, it returns the value “TRUE” otherwise “FALSE.”

AND Example 6

Recommended Articles

This is a guide to AND Function in Excel. Here we discuss the AND Formula in Excel and how to use AND in Excel and practical examples and downloadable excel templates. You can also go through our other suggested articles –

  1. OR Excel Function
  2. VBA AND
  3. Logical Functions in Excel
  4. COS Function in Excel
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Data Analysis Training (17 Courses, 8+ Projects)4.9
Excel for Marketing Training (8 Courses, 13+ Projects)4.8
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

Download AND function in Excel Template

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download AND function in Excel Template

EDUCBA

डाउनलोड AND function in Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more