Quickly highlight records in a list using multiple criteria in excel
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.
- 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 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.
- 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"
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
Related posts:
Quickly highlight records in a list in excel (AND logic)
Quickly highlight records containing text strings in excel (AND Logic)
Compare two lists of data: Highlight records existing in only one list in excel
Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel
Excel 2007: Color cells that meet criteria using conditional formatting
Recommended categories
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?