Forum Discussion
Conditional Formatting, 3 conditions met in row
Row#40 and 41 also meet the criteria, why didn't you highlight them?
Select the range A37:O48 and make a New Rule for Conditional Formatting using the formula given below and set the format as per your requirement.
=COUNTIFS($A$37:$A$48,$A37,$C$37:$C$48,$C37,$I$37:$I$48,$I37)>1
1. Select the whole data table starting from Row 37 onwards.
2. Home>Conditional Formatting> New Rule> Use a formula to determine which cells to format.
Under format values where this formula is true, write the formula as below.
=And($A37=Store,$C37=YearWeek,$I37=ProductName)
Store,YearWeek and ProductName are 3 conditions that you want to match.
All need to be defined names or absolute reference locked with $ sign for both row and column
(for e.g $A$1).
3. Press Format and under fill tab, you may change the background color you want and then press ok.
- Thank you so much for this information. I have turned this data into a table (which has helped me use PowerPivot to auto-sum this information) and will sort accordingly, anyway.
I had originally written my formula to the general song of:
=AND($A37=$A$37,$C37=$C$37,$D7=$D$37)
That didn't work...
I tried naming the ranges uniquely using both a table form and a named cell range (not a table). for some reason it isn't working.
Well, at least we think the same. My idea was the same as yours. 🙂What I thought that you want to highlight based on 3 criteria that you specify.
Now I understand that it is not what you want and what you want is to highlight all duplicate items if 3 conditions are met each other in the data table.Are you referencing the wrong cells?
The formula should be something like that.
3 criterias to meet the conditions are "MADISON", "20 2020" and "Chana Masala bowl".
=AND($A37="MADISON",$C37="20 2020",$I37="Chana Masala bowl")
It should be the right way. But, if it is still not working, I have no idea.
