This blog post shows you how to highlight rows with multiple criteria using OR logic. The criteria is found in row 3.

A row is highlighted if:

  • Date criterion is found in column B or
  • Color criterion is found in column C or
  • Product criterion is found in column D or
  • Price criterion is found in column E

Conditional formatting formula:

=SUMPRODUCT(($B$3=$B6)+($C$3=$C6)+($D$3=$D6)+($E$3=$E6))

Explaining formula in cell D11

Step 1 - Understand how relative and absolute cell references work

In cell B6 the formula is: =SUMPRODUCT(($B$3=$B6)+($C$3=$C6)+($D$3=$D6)+($E$3=$E6))

and in cell D11 the formula has changed to =SUMPRODUCT(($B$3=$B11)+($C$3=$C11)+($D$3=$D11)+($E$3=$E11))

Read more about relative and absolute cell references

Step 2 - OR logic is created using + signs between criteria

=SUMPRODUCT(($B$3=$B11)+($C$3=$C11)+($D$3=$D11)+($E$3=$E11))

becomes

=SUMPRODUCT((40566=40552)+("brown"="blue")+("Product 4"="Product 4")+(""=96,40))

becomes

=SUMPRODUCT(FALSE+FALSE+TRUE+FALSE)

becomes

=SUMPRODUCT(0+0+1+0) and returns 1. (TRUE)

Cell D11 is highlighted

How to apply conditional formatting formula

Make sure you adjust cell references to your excel sheet.

    1. Select cells B6:E27
    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(($B$3=$B6)+($C$3=$C6)+($D$3=$D6)+($E$3=$E6)) 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"

Date range and price range criteria

Let´s setup a new sheet with a date range as a criterion and a price range as another criterion.

A row is highlighted if:

  • A date is smaller or equal to cell B4 and the same date is larger or equal to cell B3 or
  • Color criterion is found in column C or
  • Product criterion is found in column D or
  • A price is smaller or equal to cell E4 and the same price is larger or equal to cell E3

Conditional formatting formula:

=SUMPRODUCT(($B$3<=$B7)*($B$4>=$B7)+($C$3=$C7)+($D$3=$D7)+($E$3<=$E7)*($E$4>=$E7))

How to apply conditional formatting formula

Make sure you adjust cell references to your excel sheet.

  1. Select cells B6:E27
  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(($B$3<=$B7)*($B$4>=$B7)+($C$3=$C7)+($D$3=$D7)+($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"

Download excel example file

quickly highlight records in a list using multiple criteria.xlsx
(Excel 2007 Workbook *.xlsx)

Functions:

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