• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

IF AND in Excel

Home » Excel » Logical Functions in Excel » IF AND in Excel

By Tejswini Bhosale Leave a Comment

IF AND Formula in excel

If and And are the two most different functions which are usually nested together, in a simple If function we can only test a single criteria but if we use the logical AND function we can test more than one criteria and give output based on the criteria, they are used as follows =IF ( Condition 1 AND Condition 2, Value if true , Value if False).

IF AND in Excel Formula (Table of Contents)

  • Formula
  • How to Use?

IF AND in Excel Formula

The usage of IF AND formula in excel is widely applied as the combination of the two logical functions enables the user to evaluate multiple conditions using AND functions and based on the outcome of AND function the IF function returns the true or false value respectively. The IF formula in excel is the logical formula used to test and compare the condition expressed with the expected value by returning the desired result if the condition is either TRUE or FALSE.

The AND function, on the other hand, is also known as a logical function, this formula is used to test multiple criteria wherein it returns TRUE if all the conditions mentioned are satisfied or else returns FALSE if either of the one criteria is false. AND function can also be used with IF formula to return the desired result.

Explanation of IF AND Formula in Excel

The below syntax will help you understand the IF AND Formula in Excel:-

#1 – AND Formula

if and formula example 1.1

  • logical 1:- This is a mandatory argument to be entered. Here the first condition to be evaluated must be entered.
  • [logical 2]:- This is an optional argument. Here the second condition to be tested should be entered. Similarly, we can enter multiple conditions by separating them by a comma“, ”.

#2 – IF Formula

if and formula 1.1

  • Logical_test:- Here the argument to be entered is mandatory. The condition to be evaluated needs to be entered in this argument. In case of IF AND combination formula, the AND function should be entered with the desired number of conditions.
  • Value_if_true:- The IF formula returns the true value if the condition entered in the logical_test argument is satisfied. In IF AND function, if all the conditions in AND formula are true then IF function will return the TRUE value which is entered in the value_if_true argument.
  • Value_if_false:- If all the conditions in AND function is false then the IF function returns the false value given in the value_if_false argument.

#3 – IF AND Formula in Excel

The syntax for IF AND formula in excel is shown below:-

if and formula 1.2

How to Use IF AND Formula in Excel?

Below are some examples of how to use IF AND Formula in Excel.

Popular Course in this category
Cyber Monday Sale
All in One Excel VBA Bundle (35 Courses with Projects) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
4.9 (1,353 ratings)
Course Price

View Course

Related Courses
VBA Macros CourseAdvanced Excel CourseTableau Certification Course
You can download this IF AND Formula Excel Template here – IF AND Formula Excel Template

IF AND Formula Excel Example #1

Consider the following table to understand the functioning of IF AND function.

if and formula example 1.1

The table has a comparative analysis for apartments to be made on the basis of the age of the building and the type of society. In this example, we would be using the combination of less than equal to excel operator and the equal to text functions in the condition to be demonstrated for IF AND function.

The function used to perform the check is

=IF(AND(B2<=2,C2=”Gated”),”Consider”,””)

Below is the screenshot of the condition applied to the table to perform the evaluation.

if and formula example 1.3

Press Enter to get the answer.

if and formula example 1.2

Drag the formula to find the answer for all apartments.

if and formula example 1.4

Explanation:- Based on the above example let us perform a check to evaluate the apartments for cell B2 less than and equal to 2 years and type of society as a gated community. So the IF AND function will be doing one of the following:-

  • If both the arguments entered in AND function is TRUE then the IF function will mark the apartment to be considered.
  • If either of the arguments is found to be FALSE or both the arguments entered in AND function is FALSE then the IF function will return a blank string.

IF AND Formula Excel Example #2

The IF AND function can also perform calculations based on whether AND function is TRUE or FALSE apart from returning only the predefined text strings.

Let us consider the below data table to understand this approach for calculating the employee hike based on the number of orders received and performance as the parameters for AND condition.

if and formula example 2.1

The syntax entered for IF AND function is as follows.

=IF(AND(B2>=200,C2=”A”),D2*10%,D2*5%)

example 2.2

Press Enter and see the final output of the above example.

example 2.3 result

Drag the formula to find the bonus of all employee.

example 2.4

Explanation:- The criteria to calculate bonus is done if the condition for a number of orders received is greater than equal to 300 and the performance is “A”. Based on the results the IF  function does the following:-

  • If both the conditions are satisfied then AND function is TRUE then the bonus received is calculated as salary multiplied by 10%.
  • If either of the one or both the conditions are found to be FALSE by AND function then the bonus is calculated as salary multiplied by 5%.

IF AND Formula Excel Example #3

As we have seen the above two examples had only two criteria’s to test and evaluate the AND conditions, but nothing is stopping you to use multiple arguments or conditions to test them for TRUE or FALSE. Let us evaluate three criteria’s in AND function in the below data table.

example 2.5 data

Referring to the data table we have five stocks with their respective financial ratios such as ROCE, ROE, Debt to equity and PE ratio. Using these parameters we will be analyzing the stocks which will have the best investment horizon for better growth. So let us apply the condition to arrive at the result.

As can be seen in the above table we have the stocks and their respective parameter details which we will be using to test the condition to invest in the suitable stocks.

The syntax used in the below table is:-

=IF(AND(B2>18%,C2>20%,D2<2,E2<30%),”Invest”,””)

example 3.1 syntax

Press Enter and see the final output of the above example.

example 3.2 output

Drag the formula to find the Investment Criteria.

example 3.3

In the above data table, the AND function tests for the parameters using the operators and the resulting output generated by the IF formula is as follows:-

  • If all the four criteria’s mentioned in AND function are tested and satisfied then IF function will be returning “Invest” text string.
  • If either one or more of the four conditions or all the four conditions fail to satisfy the AND function then the IF function will be returning empty strings.

Things to Remember

  1. The IF AND function in combination do not differentiate towards case insensitive texts that are uppercase and lower case letters.
  2. The AND function can be used to evaluate up to 255 conditions for TRUE or FALSE and the total formula length must not 8192 characters.
  3. Text values or blank cells are given as an argument to test the conditions in AND function would be ignored.
  4. The AND function will return “#VALUE!” if there is no logical output found while evaluating the conditions.

Recommended Articles

This has been a guide to IF AND in Excel. Here we discuss how to use the two logical functions IF Formula combined with AND in excel along examples and downloadable excel sheet. You may also look at these useful functions in excel –

  • Excel Text SUMIF Examples
  • SUM Shortcut in Excel
  • Excel SUMIF with Multiple Criteria
  • Nested If Condition in Excel
  • False in Excel
  • Use Logical Test in Excel
  • List of Excel Functions
  • List of Basic Excel Formulas
  • Use Nested IF in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Filed Under: Excel, Logical Functions in Excel

Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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

WallStreetMojo

Download IF AND Formula Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

CYBER WEEK OFFER - All in One Excel VBA Bundle (35 Courses with Projects) View More