Excel AND Function
The Excel AND function performs a test on two or more conditions and returns a TRUE result if all of the conditions were met or a FALSE result if one of the conditions was not met
Example: Excel AND Function
|
=AND(B9>10,B9<20)
|
Result in cell C9 (TRUE) - returns a TRUE value give all the conditions were met.
|
|
=AND(B10>10,B10<20)
|
Result in cell C10 (FALSE) - returns a FALSE value give one of the conditions was not met.
|
METHOD 2. Excel AND Function using links
EXCEL
|
=AND(B9>$C$5,B9<$C$6)
|
Result in cell C9 (TRUE) - returns a TRUE value give all the conditions were met.
|
|
=AND(B10>$C$5,B10<$C$6)
|
Result in cell C10 (FALSE) - returns a FALSE value give one of the conditions was not met.
|
METHOD 3. Excel AND function using the Excel built-in function library with hardcoded values
EXCEL
Formulas tab > Function Library group > Logical > AND > populate the input boxes
| =AND(B9>10,B9<20) Note: in this example we are populating the AND function input boxes with two conditions. Both of these conditions were met and therefore return a TRUE value. |
 |
METHOD 4. Excel AND function using the Excel built-in function library with links
EXCEL
Formulas tab > Function Library group > Logical > AND > populate the input boxes
| =AND(B9>$C$5,B9<$C$6) Note: in this example we are populating the AND function input boxes with two conditions. Both of these conditions were met and therefore return a TRUE value. |
 |
Sub Excel_AND_Function_Using_Hardcoded_Values()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("AND")
'apply the Excel AND function
ws.Range("C9") = ws.Range("B9") > 10 And ws.Range("B9") < 20
ws.Range("C10") = ws.Range("B10") > 10 And ws.Range("B10") < 20
ws.Range("C10") = ws.Range("B10") > 10 And ws.Range("B10") < 20
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named AND.
Worksheet Name: Have a worksheet named AND.
ADJUSTABLE PARAMETERS
Output Ranges: Select the output ranges by changing the cell references ("C9") and ("C10") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
METHOD 2. Excel AND function using VBA with links
VBA
Sub Excel_AND_Function_Using_Links()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("AND")
'apply the Excel AND function
ws.Range("C9") = ws.Range("B9") > ws.Range("$C$5") And ws.Range("B9") < ws.Range("$C$6")
ws.Range("C10") = ws.Range("B10") > ws.Range("$C$5") And ws.Range("B10") < ws.Range("$C$6")
ws.Range("C10") = ws.Range("B10") > ws.Range("$C$5") And ws.Range("B10") < ws.Range("$C$6")
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named AND.
Worksheet Name: Have a worksheet named AND.
ADJUSTABLE PARAMETERS
Output Ranges: Select the output ranges by changing the cell references ("C9") and ("C10") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
METHOD 3. Excel AND function with a For Loop using VBA
VBA
Sub Excel_AND_Function_Using_Links()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("AND")
'apply the Excel AND function
For x = 9 To 10
On Error Resume Next
ws.Cells(x, 3) = ws.Cells(x, 2) > ws.Range("$C$5") And ws.Cells(x, 2) < ws.Range("$C$6")
ws.Cells(x, 3) = ws.Cells(x, 2) > ws.Range("$C$5") And ws.Cells(x, 2) < ws.Range("$C$6")
Next
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named AND.
DESCRIPTION
The Excel AND function performs a test on two or more conditions and returns a TRUE result if all of the conditions were met or a FALSE result if one of the conditions was not met.
The Excel AND function performs a test on two or more conditions and returns a TRUE result if all of the conditions were met or a FALSE result if one of the conditions was not met.
SYNTAX
=AND(logical1, [logical2], ...)
=AND(logical1, [logical2], ...)
ARGUMENTS
logical1: (Required) A condition that you want to test.
logical2: (Optional) A condition that you want to test.
logical1: (Required) A condition that you want to test.
logical2: (Optional) A condition that you want to test.
ADDITIONAL NOTES
Note 1: In Excel 2007 and later the AND function can accept up to 255 logical arguments. In Excel 2003 the AND function can only accept up to 30 logical arguments.
Note 2: The AND function ignores empty cells.