Fabulous Examples of Excel Functions:
The AND Function

This tutorial teaches the AND function of Microsoft Excel. It provides examples of how how this function can be used, and also discusses using the AND and IF functions together.

The AND function asks Excel to test specific conditions and to return a value of TRUE if all of them are TRUE. If you want Excel to return a result of TRUE if at least one criterion has been met, use the OR Function.

If you like video learning, we recommend Excel 2010 Tutorial for Beginners. We have this course (over 9 hours of hands-on lessons) and think it's a great value. Some chapters are free to view online.

Author: Keynote Support

The AND Function

Use the AND function to test if all of a group of conditions are met. If so, Excel returns a value of TRUE. Otherwise, it returns a value of FALSE. The syntax for the AND function is:

=AND(test1,test2...)

… where test1, test2, ... are from 2 to 255 conditions that Excel will test that can be either TRUE or FALSE. If Excel cannot arrive at a TRUE or FALSE conclusion, it will return the #VALUE! error value.

  • =AND(3+3=6,4*2=8) ... Excel returns a value of TRUE because both conditions are met
  • =AND(1+1=2,2*2=4,12/3=8) ... the first two arguments are true, but 12÷3≠8, so Excel returns a value of FALSE

Now let's look at some worksheet examples using cell references and more complex formulas. The actual functions were entered in column A and we show the functions in column B.

Image of an Excel spreadsheet with 5 examples of the AND function

Example #1: the AND function can be used to see if the contents of a cell fall within a number range. In this example, Excel returns a value of TRUE because the value in cell D1, which is 12, is both > 10 and < 30.

Example #2: the first condition asks if cell C1 contains "Paul." In functions, you must enclose text in quotation marks. The second condition test if the contents of D1 are not equal to 10. In Excel, "not equal to" is specified by <>. Both of these conditions are met.

Example #3: the second condition makes a simple > test, but the first condition contains the SUM function. We are asking Excel if the sum of the contents of cells D1 through D3 are greater than 20. (Notice that the SUM function must be enclosed in parentheses.) Both of these conditions are met. For more information about the SUM function, see Microsoft Excel: The SUM Function.

If the second condition also contained a SUM function, the AND function would look like: =AND((SUM(D1:D3)=90),(SUM(E1:E3)=28))

Example 4: this example merely illustrates that the conditions tested can be math formulas as well. Excel returns a value of FALSE because D2+E2 is not > 10.

Example 5: in real life, some of our Excel formulas and functions can get complicated. And figuring out the parenthesis can be challenging. The first condition tests to see if the value in D1, divided by the sum of the contents of cells D1 through D3, is greater than .33. In effect, we are testing to see if Paul got more than one-third of the total points.

Study the parenthesis carefully: =AND((D1/(SUM(D1:D3)))>0.33,D1>10 )

Author: Keynote Support

The AND and IF Functions Together

Here is an example of the AND function inside an IF function. The OR function can be used similarly. For more information about the IF function, see our tutorial on Microsoft Excel Functions: The IF Function.

The IF function has three values: 1) a logical test, 2) action to take if test proves true, and 3) action to take if test proves false.

In this example, we are testing whether or not students pass a course based on two different test scores. This function asks Excel to see if the value in H3 is ≥ 500 AND the value in H4 is ≥ 800. If both conditions are met, we ask Excel to display Pass in the cell. Otherwise, we ask Excel to display Fail in the cell.

  • =IF((AND(H3>=500,H4>=800)),"Pass","Fail")

We hope this article has been helpful. Cheers!

Return to the top

Please link to this tutorial using the following HTML:

<a href="http://www.keynotesupport.com/excel-functions/and-function.shtml">Fabulous Examples of Excel Functions: The AND Function</a>