This blog post shows you how to create a conditional formatting formula and highlight matching records. You can easily change criteria by editing row 3 and 4.

### Conditional formatting formula:

=SUMPRODUCT(IF(AND(ISBLANK(\$B\$3:\$B\$4)), 1, (\$B\$3<=\$B7)*(\$B\$4>=\$B7))*(IF(ISBLANK(\$C\$3), 1, \$C\$3=\$C7))*(IF(ISBLANK(\$D\$3), 1, \$D\$3=\$D7))*(IF(AND(ISBLANK(\$E\$3:\$E\$4)), 1, (\$E\$3<=\$E7)*(\$E\$4>=\$E7))))

### How to apply conditional formatting formula

1. Select cells B7:E28
2. Click "Home" tab
3. Click "Conditional Formatting" button
4. Click "New Rule.."
5. Click "Use a formula to determine which cells to format"
6. Type =SUMPRODUCT(IF(AND(ISBLANK(\$B\$3:\$B\$4)), 1, (\$B\$3<=\$B7)*(\$B\$4>=\$B7))*(IF(ISBLANK(\$C\$3), 1, \$C\$3=\$C7))*(IF(ISBLANK(\$D\$3), 1, \$D\$3=\$D7))*(IF(AND(ISBLANK(\$E\$3:\$E\$4)), 1, (\$E\$3<=\$E7)*(\$E\$4>=\$E7))))) in "Format values where this formula is TRUE" window.
7. Click "Format.." button
8. Click "Fill" tab
9. Select a color for highlighted cells.
10. Click "Ok"
11. Click "Ok"
12. Click "Ok"

### How the conditional formatting formula works in cell C8

Step 1 - Understand relative and absolute cell referencing

Formula in cell B7:

=SUMPRODUCT(IF(AND(ISBLANK(\$B\$3:\$B\$4)), 1, (\$B\$3<=\$B7)*(\$B\$4>=\$B7))*(IF(ISBLANK(\$C\$3), 1, \$C\$3=\$C7))*(IF(ISBLANK(\$D\$3), 1, \$D\$3=\$D7))*(IF(AND(ISBLANK(\$E\$3:\$E\$4)), 1, (\$E\$3<=\$E7)*(\$E\$4>=\$E7))))

The formula in cell C8 changes to:

=SUMPRODUCT(IF(AND(ISBLANK(\$B\$3:\$B\$4)), 1, (\$B\$3<=\$B8)*(\$B\$4>=\$B8))*(IF(ISBLANK(\$C\$3), 1, \$C\$3=\$C8))*(IF(ISBLANK(\$D\$3), 1, \$D\$3=\$D8))*(IF(AND(ISBLANK(\$E\$3:\$E\$4)), 1, (\$E\$3<=\$E8)*(\$E\$4>=\$E8))))

Step 2 - First criterion, dates

I have bolded the first date criteria below:

=SUMPRODUCT(IF(AND(ISBLANK(\$B\$3:\$B\$4)), 1, (\$B\$3<=\$B8)*(\$B\$4>=\$B8))*(IF(ISBLANK(\$C\$3), 1, \$C\$3=\$C8))*(IF(ISBLANK(\$D\$3), 1, \$D\$3=\$D8))*(IF(AND(ISBLANK(\$E\$3:\$E\$4)), 1, (\$E\$3<=\$E8)*(\$E\$4>=\$E8))))

IF(AND(ISBLANK(\$B\$3:\$B\$4)), 1, (\$B\$3<=\$B8)*(\$B\$4>=\$B8))

becomes

IF(AND(ISBLANK({40554;40568])), 1, (40554<=40574)*(40568>=40574))

becomes

IF(AND({FALSE;FALSE}), 1, (TRUE)*(FALSE))

becomes

IF(FALSE), 1, (TRUE)*(FALSE))

becomes

IF(FALSE), 1,0) returns 0.

Step 3 - Second criterion, color

I have bolded the second criterion below:

=SUMPRODUCT(IF(AND(ISBLANK(\$B\$3:\$B\$4)), 1, (\$B\$3<=\$B8)*(\$B\$4>=\$B8))*(IF(ISBLANK(\$C\$3), 1, \$C\$3=\$C8))*(IF(ISBLANK(\$D\$3), 1, \$D\$3=\$D8))*(IF(AND(ISBLANK(\$E\$3:\$E\$4)), 1, (\$E\$3<=\$E8)*(\$E\$4>=\$E8))))

IF(ISBLANK(\$C\$3), 1, \$C\$3=\$C8)

becomes

IF(ISBLANK("blue"), 1, "blue"="orange")

becomes

IF(FALSE, 1, False) returns FALSE.

Step 4 - Third criterion, products

I have bolded the third criterion below:

=SUMPRODUCT(IF(AND(ISBLANK(\$B\$3:\$B\$4)), 1, (\$B\$3<=\$B8)*(\$B\$4>=\$B8))*(IF(ISBLANK(\$C\$3), 1, \$C\$3=\$C8))*(IF(ISBLANK(\$D\$3), 1, \$D\$3=\$D8))*(IF(AND(ISBLANK(\$E\$3:\$E\$4)), 1, (\$E\$3<=\$E8)*(\$E\$4>=\$E8))))

IF(ISBLANK(\$D\$3), 1, \$D\$3=\$D8)

becomes

IF(ISBLANK("Product 4"), 1, "Product 4"="Product 3")

becomes

IF(FALSE, 1, FALSE) returns FALSE.

Step 5 - Fourth criterion, price

I have bolded the fourth criteria below:

=SUMPRODUCT(IF(AND(ISBLANK(\$B\$3:\$B\$4)), 1, (\$B\$3<=\$B8)*(\$B\$4>=\$B8))*(IF(ISBLANK(\$C\$3), 1, \$C\$3=\$C8))*(IF(ISBLANK(\$D\$3), 1, \$D\$3=\$D8))*(IF(AND(ISBLANK(\$E\$3:\$E\$4)), 1, (\$E\$3<=\$E8)*(\$E\$4>=\$E8))))

IF(AND(ISBLANK(\$E\$3:\$E\$4)), 1, (\$E\$3<=\$E8)*(\$E\$4>=\$E8))

becomes

IF(AND(ISBLANK({15;25})), 1, (15<=67,8)*(25>=67,8))

becomes

IF(AND({FALSE;FALSE}), 1, (TRUE)*(FALSE))

becomes

IF(FALSE, 1, 0) returns 0.

Step 6 - All criteria together

=SUMPRODUCT(IF(AND(ISBLANK(\$B\$3:\$B\$4)), 1, (\$B\$3<=\$B7)*(\$B\$4>=\$B7))*(IF(ISBLANK(\$C\$3), 1, \$C\$3=\$C7))*(IF(ISBLANK(\$D\$3), 1, \$D\$3=\$D7))*(IF(AND(ISBLANK(\$E\$3:\$E\$4)), 1, (\$E\$3<=\$E7)*(\$E\$4>=\$E7))))

becomes

=SUMPRODUCT(0*FALSE*FALSE*0)

becomes

=SUMPRODUCT(0) returns 0. Row 8 is not highlighted.

Final notes

The formula also works if you only have one criterion or two criteria. Try it yourself.

quickly highlight records in a list AND logic.xlsx
(Excel 2007 Workbook *.xlsx)

### Functions:

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays

IF(logical_test, [value_if_true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ISBLANK(value)
Checks whether a reference is to an empty cell and returns TRUE or FALSE