Using logical functions in Excel: AND, OR, XOR and NOT

The tutorial explains the essence of Excel logical functions AND, OR, XOR and NOT and provides formula examples that demonstrate their common and inventive uses.

Last week we tapped into the insight of Excel logical operators that are used to compare data in different cells. Today, you will see how to extend the use of logical operators and construct more elaborate tests to perform more complex calculations. Excel logical functions such as AND, OR, XOR and NOT will help you in doing this.

Excel logical functions - overview

Microsoft Excel provides 4 logical functions to work with the logical values. The functions are AND, OR, XOR and NOT. You use these functions when you want to carry out more than one comparison in your formula or test multiple conditions instead of just one. As well as logical operators, Excel logical functions return either TRUE or FALSE when their arguments are evaluated.

The following table provides a short summary of what each logical function does to help you choose the right formula for a specific task.

Function Description Formula Example Formula Description
AND Returns TRUE if all of the arguments evaluate to TRUE. =AND(A2>=10, B2<5) The formula returns TRUE if a value in cell A2 is greater than or equal to 10, and a value in B2 is less than 5, FALSE otherwise.
OR Returns TRUE if any argument evaluates to TRUE. =OR(A2>=10, B2<5) The formula returns TRUE if A2 is greater than or equal to 10 or B2 is less than 5, or both conditions are met. If neither of the conditions it met, the formula returns FALSE.
XOR Returns a logical Exclusive Or of all arguments. =XOR(A2>=10, B2<5) The formula returns TRUE if either A2 is greater than or equal to 10 or B2 is less than 5. If neither of the conditions is met or both conditions are met, the formula returns FALSE.
NOT Returns the reversed logical value of its argument. I.e. If the argument is FALSE, then TRUE is returned and vice versa. =NOT(A2>=10) The formula returns FALSE if a value in cell A1 is greater than or equal to 10; TRUE otherwise.

In additions to the four logical functions outlined above, Microsoft Excel provides 3 "conditional" functions - IF, IFERROR and IFNA.

Excel logical functions - facts and figures

  1. In arguments of the logical functions, you can use cell references, numeric and text values, Boolean values, comparison operators, and other Excel functions. However, all arguments must evaluate to the Boolean values of TRUE or FALSE, or references or arrays containing logical values.
  2. If an argument of a logical function contains any empty cells, such values are ignored. If all of the arguments are empty cells, the formula returns #VALUE! error.
  3. If an argument of a logical function contains numbers, then zero evaluates to FALSE, and all other numbers including negative numbers evaluate to TRUE. For example, if cells A1:A5 contain numbers, the formula =AND(A1:A5) will return TRUE if none of the cells contains 0, FALSE otherwise.
  4. A logical function returns the #VALUE! error if none of the arguments evaluate to logical values.
  5. A logical function returns the #NAME? error if you've misspell the function's name or attempted to use the function in an earlier Excel version that does not support it. For example, the XOR function can be used in Excel 2016 and 2013 only.
  6. In Excel 2007 and higher, you can include up to 255 arguments in a logical function, provided that the total length of the formula does not exceed 8,192 characters. In Excel 2003 and lower, you can supply up to 30 arguments and the total length of your formula shall not exceed 1,024 characters.

Using the AND function in Excel

The AND function is the most popular member of the logic functions family. It comes in handy when you have to test several conditions and make sure that all of them are met. Technically, the AND function tests the conditions you specify and returns TRUE if all of the conditions evaluate to TRUE, FALSE otherwise.

The syntax for the Excel AND function is as follows:

AND(logical1, [logical2], …)

Where logical is the condition you want to test that can evaluate to either TRUE or FALSE. The first condition (logical1) is required, subsequent conditions are optional.

And now, let's look at some formula examples that demonstrate how to use the AND functions in Excel formulas.

Formula Description
=AND(A2="Bananas", B2>C2) Returns TRUE if A2 contains "Bananas" and B2 is greater than C2, FALSE otherwise.
=AND(B2>20, B2=C2) Returns TRUE if B2 is greater than 20 and B2 is equal to C2, FALSE otherwise.
=AND(A2="Bananas", B2>=30, B2>C2) Returns TRUE if A2 contains "Bananas", B2 is greater than or equal to 30 and B2 is greater than C2, FALSE otherwise.

Using the AND function in Excel formulas

Excel AND function - common uses

By itself, the Excel AND function is not very exciting and has narrow usefulness. But in combination with other Excel functions, AND can significantly extend the capabilities of your worksheets.

One of the most common uses of the Excel AND function is found in the logical_test argument of the IF function to test several conditions instead of just one. For example, you can nest any of the AND functions above inside the IF function and get a result similar to this:

=IF(AND(A2="Bananas", B2>C2), "Good", "Bad")
An example of the IF formula with a nested AND function

For more IF / AND formula examples, please check out his tutorial: Excel IF function with multiple AND conditions.

An Excel formula for the BETWEEN condition

If you need to create a between formula in Excel that picks all values between the given two values, a common approach is to use the IF function with AND in the logical test.

For example, you have 3 values in columns A, B and C and you want to know if a value in column A falls between B and C values. To make such a formula, all it takes is the IF function with nested AND and a couple of comparison operators:

Formula to check if X is between Y and Z, inclusive:

=IF(AND(A2>=B2,A2<=C2),"Yes", "No")

Formula to check if X is between Y and Z, not inclusive:

=IF(AND(A2>B2, A2<C2),"Yes", "No")
An Excel formula for the BETWEEN condition

As demonstrated in the screenshot above, the formula works perfectly for all data types - numbers, dates and text values. When comparing text values, the formula checks them character-by-character in the alphabetic order. For example, it states that Apples in not between Apricot and Bananas because the second "p" in Apples comes before "r" in Apricot. Please see Using Excel comparison operators with text values for more details.

As you see, the IF /AND formula is simple, fast and almost universal. I say "almost" because it does not cover one scenario. The above formula implies that a value in column B is smaller than in column C, i.e. column B always contains the lower bound value and C - the upper bound value. This is the reason why the formula returns "No" for row 6, where A6 has 12, B6 - 15 and C6 - 3 as well as for row 8 where A8 is 24-Nov, B8 is 26-Dec and C8 is 21-Oct.

But what if you want your between formula to work correctly regardless of where the lower-bound and upper-bound values reside? In this case, use the Excel MEDIAN function that returns the median of the given numbers (i.e. the number in the middle of a set of numbers).

So, if you replace AND in the logical test of the IF function with MEDIAN, the formula will go like:

=IF(A2=MEDIAN(A2:C2),"Yes","No")

And you will get the following results:
Using IF with the MEDIAN function to find out all values between the given two values

As you see, the MEDIAN function works perfectly for numbers and dates, but returns the #NUM! error for text values. Alas, no one is perfect : )

If you want a perfect Between formula that works for text values as well as for numbers and dates, then you will have to construct a more complex logical text using the AND / OR functions, like this:

=IF(OR(AND(A2>B2, A2<C2), AND(A2<B2, A2>C2)), "Yes", "No")
An Excel Between formula that works for text values as well as for numbers and dates

Using the OR function in Excel

As well as AND, the Excel OR function is a basic logical function that is used to compare two values or statements. The difference is that the OR function returns TRUE if at least one if the arguments evaluates to TRUE, and returns FALSE if all arguments are FALSE. The OR function is available in all versions of Excel 2016 - 2000.

The syntax of the Excel OR function is very similar to AND:

OR(logical1, [logical2], …)

Where logical is something you want to test that can be either TRUE or FALSE. The first logical is required, additional conditions (up to 255 in modern Excel versions) are optional.

And now, let's write down a few formulas for you to get a feel how the OR function in Excel works.

Formula Description
=OR(A2="Bananas", A2="Oranges") Returns TRUE if A2 contains "Bananas" or "Oranges", FALSE otherwise.
=OR(B2>=40, C2>=20) Returns TRUE if B2 is greater than or equal to 40 or C2 is greater than or equal to 20, FALSE otherwise.
=OR(B2=" ", C2="") Returns TRUE if either B2 or C2 is blank or both, FALSE otherwise.

Using the OR function in Excel

As well as Excel AND function, OR is widely used to expand the usefulness of other Excel functions that perform logical tests, e.g. the IF function. Here are just a couple of examples:

IF function with nested OR

=IF(OR(B2>30, C2>20), "Good", "Bad")

The formula returns "Good" if a number in cell B3 is greater than 30 or the number in C2 is greater than 20, "Bad" otherwise.

Excel AND / OR functions in one formula

Naturally, nothing prevents you from using both functions, AND & OR, in a single formula if your business logic requires this. There can be infinite variations of such formulas that boil down to the following basic patterns:

=AND(OR(Cond1, Cond2), Cond3)

=AND(OR(Cond1, Cond2), OR(Cond3, Cond4)

=OR(AND(Cond1, Cond2), Cond3)

=OR(AND(Cond1,Cond2), AND(Cond3,Cond4))

For example, if you wanted to know what consignments of bananas and oranges are sold out, i.e. "In stock" number (column B) is equal to the "Sold" number (column C), the following OR/AND formula could quickly show this to you:

=OR(AND(A2="bananas", B2=C2), AND(A2="oranges", B2=C2))
The AND/OR formula to test multiple conditions

OR function in Excel conditional formatting

=OR($B2="", $C2="")

The rule with the above OR formula highlights rows that contain an empty cell either in column B or C, or in both.
Using the OR function in Excel conditional formatting

For more information about conditional formatting formulas, please see the following articles:

Using the XOR function in Excel

In Excel 2013, Microsoft introduced the XOR function, which is a logical Exclusive OR function. This term is definitely familiar to those of you who have some knowledge of any programming language or computer science in general. For those who don't, the concept of 'Exclusive Or' may be a bit difficult to grasp at first, but hopefully the below explanation illustrated with formula examples will help.

The syntax of the XOR function is identical to OR's :

XOR(logical1, [logical2],…)

The first logical statement (Logical 1) is required, additional logical values are optional. You can test up to 254 conditions in one formula, and these can be logical values, arrays, or references that evaluate to either TRUE or FALSE.

In the simplest version, an XOR formula contains just 2 logical statements and returns:

  • TRUE if either argument evaluates to TRUE.
  • FALSE if both arguments are TRUE or neither is TRUE.

This might be easier to understand from the formula examples:

Formula Result Description
=XOR(1>0, 2<1) TRUE Returns TRUE because the 1st argument is TRUE and the 2nd argument is FALSE.
=XOR(1<0, 2<1) FALSE Returns FALSE because both arguments are FALSE.
=XOR(1>0, 2>1) FALSE Returns FALSE because both arguments are TRUE.

When more logical statements are added, the XOR function in Excel results in:

  • TRUE if an odd number of the arguments evaluate to TRUE;
  • FALSE if is the total number of TRUE statements is even, or if all statements are FALSE.

The screenshot below illustrates the point:
Excel XOR formula with multiple logical statements

If you are not sure how the Excel XOR function can be applied to a real-life scenario, consider the following example. Suppose you have a table of contestants and their results for the first 2 games. You want to know which of the payers shall play the 3rd game based on the following conditions:

  • Contestants who won Game 1 and Game 2 advance to the next round automatically and don't have to play Game 3.
  • Contestants who lost both first games are knocked out and don't play Game 3 either.
  • Contestants who won either Game 1 or Game 2 shall play Game 3 to determine who goes into the next round and who doesn't.

A simple XOR formula works exactly as we want:

=XOR(B2="Won", C2="Won")
Using the Excel XOR function in a real-life scenario

And if you nest this XOR function into the logical test of the IF formula, you will get even more sensible results:

=IF(XOR(B2="Won", C2="Won"), "Yes", "No")
The IF formula with a nested XOR function

Using the NOT function in Excel

The NOT function is one of the simplest Excel functions in terms of syntax:

NOT(logical)

You use the NOT function in Excel to reverse a value of its argument. In other words, if logical evaluates to FALSE, the NOT function returns TRUE and vice versa. For example, both of the below formulas return FALSE:

=NOT(TRUE)

=NOT(2*2=4)

Why would one want to get such ridiculous results? In some cases, you might be more interested to know when a certain condition isn't met than when it is. For example, when reviewing a list of attire, you may want to exclude some color that does not suit you. I'm not particularly fond of black, so I go ahead with this formula:

=NOT(C2="black")
Using the NOT function in Excel

As usual, in Microsoft Excel there is more than one way to do something, and you can achieve the same result by using the Not equal to operator: =C2<>"black".

If you want to test several conditions in a single formula, you can use NOT in conjunctions with the AND or OR function. For example, if you wanted to exclude black and white colors, the formula would go like:

=NOT(OR(C2="black", C2="white"))

And if you'd rather not have a black coat, while a black jacket or a back fur coat may be considered, you should use NOT in combination with the Excel AND function:

=NOT(AND(C2="black", B2="coat"))

Another common use of the NOT function in Excel is to reverse the behavior of some other function. For instance, you can combine NOT and ISBLANK functions to create the ISNOTBLANK formula that Microsoft Excel lacks.

As you know, the formula =ISBLANK(A2) returns TRUE of if the cell A2 is blank. The NOT function can reverse this result to FALSE: =NOT(ISBLANK(A2))

And then, you can take a step further and create a nested IF statement with the NOT / ISBLANK functions for a real-life task:

=IF(NOT(ISBLANK(C2)), C2*0.15, "No bonus :(")
A nested IF statement with NOT / ISBLANK functions

Translated into plain English, the formula tells Excel to do the following. If the cell C2 is not empty, multiply the number in C2 by 0.15, which gives the 15% bonus to each salesman who has made any extra sales. If C2 is blank, the text "No bonus :(" appears.

In essence, this is how you use the logical functions in Excel. Of course, these examples have only scratched the surface of AND, OR, XOR and NOT capabilities. Knowing the basics, you can now extend your knowledge by tackling your real tasks and writing smart elaborate formulas for your worksheets.

Table of contents

567 comments

  1. I have three colms as
    A1 (City), B1 (Quantity), C1 (Amount)

    If Colm A1 is Karachi, I want then quantity (B1) should multiply with 15 in Amount colm (C1)

    If Colm A1 is Lahore, I want then quantity (B1) should multiply with 15 in Amount colm (C1)

    • Hello, Azam,

      Please try the following formula:

      =IF(A1="Karachi", B1*15,IF(A1="Lahore", B1*15,""))

      Hope it will help you.

  2. I need a formula to calculate a variable sales commision. if I sell 8 or less, I get $x. if I sell 9-15 units, ill get $y. with a variability of 5 different pay rates for commission.
    Thanks

    • Hello, Patrick,

      Please try the following formula:

      =IFS(A1<=8,"$x",A1<=15,"$y",A1<=20,"$z",A1<=25,"$v",A1<=30,"$w")

      Hope it will help you.

  3. Hi,
    I need a formula to calculate a variable sales commision. if I sell 8 or less, I get $x. if I sell 9-15 units, ill get $y. with a variability of 5 different pay rates for commission.
    Thanks

  4. hi...can i ask for help..how can i solve this logic delta column:

    if stock > max >>> stock - max (the value must be highlighted in red)
    if min <=stock >> stock quantity (the value must be highlighted in green)
    if stock>> stock-min (the value must be highlighted in yellow)

    • Hello, jelmer,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  5. some plz help me to use this

    400,001 to 500,000 Slab 02 (AZ163-400000)*2%
    500,001 to 750,000 Slab 03 2000+(AZ164-500000)*5%
    750,001 to 1,400,000 Slab 04 14500+((AZ165-750000)*10%)
    1,400,001 to 1,500,000 Slab 05 79500+((AZ166-1400000)*12.5%)
    1,500,001 to 1,800,000 Slab 06 92000+((AZ167-1500000)*15%)
    1,800,001 to 2,500,000 Slab 07 137000+((AZ168-1800000)*17.5%)
    2,500,001 to 3,000,000 Slab 08 259500+((AZ169-2500000)*20%)
    3,000,001 to 3,500,000 Slab 09 359500+((AZ170-3000000)*22.5%)
    3,500,001 to 4,000,000 Slab 10 472000+((AZ171-3500000)*25%)
    4,000,001 to 7,000,000 Slab 11 597000+((AZ172-4000000)*27.5%)
    Exceeds from 7,000,000 Slab 12 1422000+((AZ173-7000000)*30%)

  6. IF(AND(F47<=22500000,F4750000000,F47<=100000000),0,E43*0.1))

    I AM USING THIS FORMULA BUT THE RESULT IS NOT GIVEN WHERE IS WRONG
    PLEASE GIVE ME SALUTATION

  7. IF(AND(F47<=22500000,F4750000000,F47<=100000000),0,E43*0.1))

    I AM USING THIS FORMULA BUT THE RESULT IS NOT GIVEN WHERE IS WRONG
    PLEASE GIVE ME SALUTATION

  8. hell can you help me. i sen message to facebook and come friend.

  9. I have data like this

    123415678
    432128765
    001218090

    These numbers represents ID numbers If the 5th number is 1 then the ID belongs to a male if its 2 then the ID belongs to a female

    • Hello ,

      Go the next column, and use this formula (=Left(A1,5))
      in Column C , put this formula ( =Right(C1,1)

      Codes Left Coding Right Coding
      123415678 """=LEFT(A2,5) """=RIGHT(B2,1)
      432128765 43212 2
      301218090 30121 1
      214510025 21451 1
      301228090 30122 2
      301228090 30122 2
      301218090 30121 1

    • Hello ,

      Go the next column, and use this formula (=Left(A1,5))
      in Column C , put this formula ( =Right(C1,1)

      Codes Left Coding Right Coding Sex
      123415678 """=LEFT(A2,5) """=RIGHT(B2,1) ''''=IF(C2="2","Female","Male")
      432128765 43212 2 Female
      301218090 30121 1 Male
      214510025 21451 1 Male
      301228090 30122 2 Female
      301228090 30122 2 Female
      301218090 30121 1 Male

  10. Wanted to extend my thanks. This is exactly what I was looking for!

  11. Using excel formulae, find out the following
    1. How many have neither registered nor completed any of the 3 courses?
    2. How many have registered or trained in atleast 2 of the 3 courses?
    3. How many have not been trained in any of the 3 yet?

    R - Registered for training (training not done yet)
    T - Trained
    Blank - Neither

    Name SQL SAS Excel
    Prakash R T
    Rahul R
    Rajiv
    Priya R T
    Amit T

  12. =IF(NOT(isblank (M6)), "unaccomplished", if (isblank N6))' unaccomplsihed
    can somebody help me?

  13. I have an excel sheet named "ALL".Just trying to apply formula for counting open points related to Business Development.I applied below formula but its not working can you please check & confirm

    =COUNT(AND(All!K4:K1000="Business Development",All!N4:N1000="Open"))

  14. Hello, I need help. I need to verify whether my column C's (numeric) information and column B's (description) information are appropriate and accurate for my column A's (code). Thanks in advance.

  15. Hi,
    please solve this question.
    If Mr.A has 125 coins then he will get "1 Apple" and if Mr.A has 170 coins then he will get "1 Apple" for 125 coins and on balance 45 coins, he will get "1 Lemon" on every 25 coins.

    Please convert the same in to logical formula in excel.

    • =IF(G14="Mr.A",(IFS(H14=125,"1 Apple",H14=170,"1 Apple, 1 Lemon"))," ")

      Hope this will help you

    • LET CELL A1 have the coins MR. A HAS, THEN copy THE following EXPRESSION
      =CONCATENATE("IF MR.A HAS 125 COINS, THE HE WILL GET 1 APPLE, AND IF MR. A HAS"," ",+A1," ","COINS"," ","THEN HE WILL GET"," ",ROUNDDOWN(+A1/125,0)," ","APPLE(S)"," ","AND ON BALANCE"," ",A1-(ROUNDDOWN(+A1/125,0)*125)," ","COINS, HE WILL GET"," ",ROUNDDOWN(+(A1-(+ROUNDDOWN(+A1/125,0)*125))/45,0)," ","LEMON ON EVERY 25 COINS")

  16. =IF(AND(V17="B",V18<="470"),"127","0"),IF(AND(V17="BC",V18<="700"),"112","127"),IF(AND(V17="C",V18<="420"),"0","112")

    I AM USING THIS FORMULA BUT THE RESULT IS " #VALUE! "
    PLEASE GIVE ME SALUTATION.

    • Muhammad, Possible Solution
      1) To make this easier, separate the formulas and evaluate each one separately. I noticed that there is overlapping logic. If formula #1 is False you get a -0- and if formula #3 is True you get a -0-, and the same thing with 127 and 112 across all 3 formulas. How would you know which values gave you the answer? Also, what if the input values do not match your expected answers, do you evaluate them? And your attempt to nest the IF statements is wrong.
      Nesting as below will work but you will still only get 112, 127 or -0-.
      =IF(AND(B1="B",B2<="470"),"127",IF(AND(B1="BC",B2<="700"),"112","0"))
      Hope this gives you some insight.

  17. Hi
    I have 9 sheets in the same workbook variously populated with 0 and 1. In a separate sheet I want to fill a cell with "1" if 1 occurs in the same cell in any of the 9 sheets, or "0" if it doesn't occur at all. I have tried using the following:

    =IF(OR('Training NetworkB'!C5="1",KnowledgeSharingBtxt!C5="1",'Encouragement NetworkB'!C5="1",'Organisation NetworkB'!C5="1",'Monitoring NetworkB'!C5="1",'Networking NetB'!C5="1",'Labour sharing NetworkB'!C5="1",'Conflict NetworkB'!C5="1",'Phys_Finan Capital NetB'!C5="1"),"1","0")

    It returns a 0 in all cells, even if a 1 occurs. Can you help at all?
    Many thanks in advance

    • Hi,
      It looks to me like you have a space after all your 1's that are in your speech marks. If the cells on the other spreadsheets just contain a "1" and not a "1 " then the formula won't recognise the 1 because it will be looking for a 1 and a space.

    • Hi,
      It looks to me like you have a space after all your 1's that are in your speech marks. If the cells on the other spreadsheets just contain a "1" and not a "1 " then the formula won't recognise the 1 because it will be looking for a 1 and a space.

  18. Hi,
    I need to make a formula to count 3 different columns but counting just one time if they have more than one value for the others 2 columns.
    How I can do that??
    I try different ways but I don't know do it!
    Thanks!!

  19. hi
    i need a formula for. .

    if found 0152 then get 18
    &
    if found 6305 then get 5

    • Hi

      Thank you for contacting us.

      Please try the following formula:

      =IF(A1 = 152, 18, IF(A1=6305, 5, " "))

      I hope this helps. Please let me know if you have any other questions or difficulties.

      • Hi Mary ,
        can you help me in macro how to record macro ?

        • Hi Amit,

          First you have to save as your Excel sheet to .xlsm. after this step you have to go view ribbon and choose Macro option. There you can find macro recording.

          Start recording and work in the same sheet without using mouse, it works perfectly. Because if you use mouse Macro may not write program as per your clicks.

          Once you stop recording you just give an non-calculated report which you did earlier and run macro from same macro option.

          Regards,

          Mohan

  20. Hi Svetlana
    I need a formula for comparison of numbers with text
    E.g.
    If x is less than or equal to 1600 them yes or if x is not available then "not avl"
    But if X is more than 1600 then wow but if x is not avl then not avl

    • Hi 201, please use below Formula

      =IFERROR(IF(VLOOKUP(D1,A1:B13,2,0)>1600,"Wow",IF(VLOOKUP(D1,$A$1:$B$13,2,0)<=1600,"Yes","0")),"Not Avl")

    • '=IF(+C6=0,"not avl",IF(+C61600,"wow")))

    • =IF(C18>1600,"Wow",IF(C18>0,"yes",Not Available))

      • =IF(C18>1600,"Wow",IF(C18>0,"yes","Not Available"))

        • Hi Please use this.
          =IF(OR(C24="",),"Not Available",IF(C24>=1600,"Yes","Wow"))

    • =IF(OR(B6="",B6<=1),"Not Available",IF(B6<=1600,"Yes","Wow"))

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)