Author: Oscar Cronquist Article last updated on January 14, 2019

The image above shows you how to highlight rows with multiple criteria using OR logic. The criteria are 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. Press with left mouse button on "Home" tab
    3. Press with left mouse button on "Conditional Formatting" button
    4. Press with left mouse button on "New Rule.."
    5. Press with left mouse button on "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. Press with left mouse button on "Format.." button
    8. Press with left mouse button on "Fill" tab
    9. Select a color for highlighted cells.
    10. Press with left mouse button on "Ok"
    11. Press with left mouse button on "Ok"
    12. Press with left mouse button on "Ok"
Note: Conditional formatting is volatile meaning when the worksheet is recalculated all conditionally formatted cells are recalculated, this may slow down your worksheet considerably.

Date range and price range criteria

Let's set up 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. Press with left mouse button on "Home" tab
  3. Press with left mouse button on "Conditional Formatting" button
  4. Press with left mouse button on "New Rule.."
  5. Press with left mouse button on "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. Press with left mouse button on "Format.." button
  8. Press with left mouse button on "Fill" tab
  9. Select a color for highlighted cells.
  10. Press with left mouse button on "Ok"
  11. Press with left mouse button on "Ok"
  12. Press with left mouse button on "Ok"

Get the Excel file


quickly-highlight-records-in-a-list-using-multiple-criteria.xlsx

Weekly Blog EMAIL

[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

Highlight records containing text strings (AND Logic)

The picture above shows you how to highlight rows containing text strings using conditional formatting. Example, continents criterion (cell B3) […]

Cash drawer bill extractor

Question: I need to setup a template to remove the largest available (Qty and denomination)bills and leave exactly $150 back […]

Dynamic team generator

Mark G asks: 1 - I see you could change the formula to have the experssion COUNTIF($C$1:C1, $E$2:$E$5)<5 changed so […]

List permutations with repetition [UDF]

This blog post demonstrates a custom function (UDF) that creates permutations. Repetition is allowed. The custom function lets you specify the […]

Compare tables: Highlight records not in both tables

The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]

Filter unique distinct records with a condition

Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a […]