Forum Discussion
Conditional Formatting, 3 conditions met in row
Hi,
I need the row highlighted where 3 conditions are the same:
1. YearWeek = YearWeek (Col C)
2. ProductName = Product Name (Col I)
3. Store = Store (Col A)
Example: Rows 45 & 47 have these criteria met. I'd like that row highlighted.
I have an XLookup to run on rows of data and am stumped as it only gets the first value if these conditions are met and need to highlight and manually sort the rows or come up with a solution. Outside of using "GetPivotData" and summing based on date or creating a sum column using PowerQuery, I don't know what to do. I'm hoping for a conditional formatting solution to highlight and manually sum the information (a ton).
Thank you,
Robin
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
7 Replies
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.
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)>1Thank you for your support. I appreciate the help.
I didn't highlight the rows because I wanted to provide an example. This workbook has over 19,000 rows of data in it.
I have added the conditional formatting to my spreadsheet. It works perfectly.
Thank you for your response.
I'm a lousy conditional formatting user and appreciate your expertise, very much!
Robin
You're welcome Robinmf1979! Glad it worked as desired.
If that takes care of your original question, please take a minute to accept the post with the proposed solution as a Best Response to mark your question as Solved.
