Forum Discussion
Color dates if more than 1 occurrence of some numbers in a column
I require help with this formula. I want Excel to highlight row 8 in red if cells C9 through C16 have more than one occurrence of numbers 1, 2, 3, or 5 input (refer to the color chart in the attached image). Currently, the row only highlights the day of the week if it is the same number in the column. I need the row to be highlighted with any combination of 1, 2, 3, or 5 if they appear in the same row. This is for tracking scheduling purposes and will indicate if there are multiple individuals on vacation, sick, or leaving early. Please see the example attached. Columns F and G should be highlighted, but the formula does not recognize them unless it is the same number in a column.
=OR(COUNTIF(C9:C16,1)>1,COUNTIF(C9:C16,2)>1,COUNTIF(C9:C16,3)>1)
Missed/forgot something in my previous post (will delete it...) - Sorry:
Conditional Formatting rule that applies to $D$7:$L$7 with the above setup:
=SUM(COUNTIF(D$9:D$16,Arr_DaysOff)) > 1where Arr_DaysOff is a defined name (in Name Manager) with:
={1,2,3,5}
8 Replies
Here is an attempt with this adapted formula:
=SUM(COUNTIFS(C9:C16, {1, 2, 3, 5})) > 1
This formula counts the occurrences of numbers 1, 2, 3, or 5 in cells C9 through C16. If the total count of these numbers is greater than 1, it returns TRUE, indicating that the row should be highlighted.
Hope it helps.
- Thank you, I was not able to get this to work in conditional formatting.
Received the following:
You may not use reference operators (such as unions, intersections, and ranges), array constants, or the LAMBDA function for conditional formatting criteria.
- Could you revise the title of this discussion to help people who Search for existing solutions please (+ Thanks)? i.e. Color dates if more than 1 occurrence of some numbers in a column
- If you're not allowed to attach a file yet you can share it with i.e. https://support.microsoft.com/en-us/office/share-files-with-onedrive-4e871e9a-4cb7-4c66-8b38-d2ee590532c2, Google Drive or the like and post the shared link here
- I read your problem description several times and must admit I remain confused with a couple of things (i.e. why you refer to C9:C16 - that column appears to be empty in your pic.) so the following might not be what you expect (reflects what I understood):
In attached file and based on the above setup a Conditional Format. rule with the following formula applies to D7:L7:
=SUM(--(COUNTIF(D$9:D$16, Arr_DaysOff) > 1))where Arr_DaysOff is a defined name (in Name Manager) with:
={1,2,3,5}Hope this helps & if this isn't what you expect please clarify/re-explain - Thanks
- Attempted sending file in a direct message.
- This is close to what I am looking for. I would like for Row 7 to be highlight for any combination of numbers 1,2,3,5 in a column if their is more than one occurrence.
Using your work as an example I would like column 5 & 8 to highlight as well but not column 2 as it only has one occurrence. Hoping this clears up what I am looking for the file to do.
I am working on OneDrive so I can upload my file but having difficulties. Thanks.Missed/forgot something in my previous post (will delete it...) - Sorry:
Conditional Formatting rule that applies to $D$7:$L$7 with the above setup:
=SUM(COUNTIF(D$9:D$16,Arr_DaysOff)) > 1where Arr_DaysOff is a defined name (in Name Manager) with:
={1,2,3,5}
- Please attach a sample file so that we can work on that.
Harun24HR, I am unable to upload the file to the discussion. I am think this is due to me becoming a new member to the community from what I am seeing online. I am not certain when I would have that permission. I will see if I can come up with another solution to provide the file.
