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
- Press with left mouse button on "Home" tab
- Press with left mouse button on "Conditional Formatting" button
- Press with left mouse button on "New Rule.."
- Press with left mouse button on "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.
- Press with left mouse button on "Format.." button
- Press with left mouse button on "Fill" tab
- Select a color for highlighted cells.
- Press with left mouse button on "Ok"
- Press with left mouse button on "Ok"
- Press with left mouse button on "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
- Press with left mouse button on "Home" tab
- Press with left mouse button on "Conditional Formatting" button
- Press with left mouse button on "New Rule.."
- Press with left mouse button on "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.
- Press with left mouse button on "Format.." button
- Press with left mouse button on "Fill" tab
- Select a color for highlighted cells.
- Press with left mouse button on "Ok"
- Press with left mouse button on "Ok"
- Press with left mouse button on "Ok"
Cf search category
In this article, I will demonstrate how to search a table using conditional formatting. The criteria highlight matching column and […]
The picture above shows you how to highlight rows containing text strings using conditional formatting. Example, continents criterion (cell B3) […]
The picture above shows you conditional formatting formula that highlights matching records based on criteria in row 3 and 4. […]
Conditional formatting category
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
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 […]
The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Highlight […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]
Question: I have a list that I keep adding rows to. How do i create a border that expands as […]
In this article, I will demonstrate how to search a table using conditional formatting. The criteria highlight matching column and […]
Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]
The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, […]
A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]
In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]
The image above shows conditional formatting highlighting unique distinct values, duplicates are not highlighted. Conditional Formatting Formula: =COUNTIF($B$3:B3, B3)=1 The […]
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]
Functions in this article
More than 1300 Excel formulas
Conditional Formatting categories
Excel categories
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 […]