May 22 2024 02:58 PM - edited May 23 2024 12:13 PM
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)
May 22 2024 07:09 PM
May 22 2024 07:32 PM
@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.
May 23 2024 12:32 AM
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
May 23 2024 02:45 AM
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.
May 23 2024 12:17 PM
May 23 2024 12:19 PM
May 23 2024 02:19 PM
Solution
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)) > 1
where Arr_DaysOff is a defined name (in Name Manager) with:
={1,2,3,5}
May 23 2024 02:19 PM
Solution
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)) > 1
where Arr_DaysOff is a defined name (in Name Manager) with:
={1,2,3,5}