Highlight records – multiple criteria [OR logic]
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:
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.
-
- Select cells B6:E27
- Click "Home" tab
- Click "Conditional Formatting" button
- Click "New Rule.."
- Click "Use a formula to determine which cells to format"
- Type =SUMPRODUCT(($B$3=$B6)+($C$3=$C6)+($D$3=$D6)+($E$3=$E6)) in "Format values where this formula is TRUE" window.
- Click "Format.." button
- Click "Fill" tab
- Select a color for highlighted cells.
- Click "Ok"
- Click "Ok"
- Click "Ok"
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:
How to apply conditional formatting formula
Make sure you adjust cell references to your excel sheet.
- Select cells B6:E27
- Click "Home" tab
- Click "Conditional Formatting" button
- Click "New Rule.."
- Click "Use a formula to determine which cells to format"
- 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.
- Click "Format.." button
- Click "Fill" tab
- Select a color for highlighted cells.
- Click "Ok"
- Click "Ok"
- Click "Ok"
Highlight overlapping date ranges using conditional formatting
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]
Count Conditionally Formatted cells
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
Highlight records based on overlapping date ranges and a condition
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
Highlight dates in a date range
Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]
This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]
4 Responses to “Highlight records – multiple criteria [OR logic]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hello, running into a predicament. I have made a planner for my college courses and flight schedule (going through flight school at an SEC university) BUT it had took me some time to make and I would like to make it easier for me in the future.
What I have tried to accomplish with no realistic result, a conditional format (first tried "DATA BARS" then HIGHLIGHTED CELL RULE") to highlight the start/end times, start/end dates, and frequency of multiple classes.
My desired end result would be to type in a class start/end date, start/end time, choose a frequency, and voila, my schedule is made.
Thanks in advance.
Clint
Clint,
can you provide some example data and the desired outcome?
[…] has some incredible tools for highlighting cells, rows, dates, comparing data and even series in line charts. A technique using the secondary axis […]
[…] Quickly highlight records in a list using multiple criteria in excel […]