Hi All,
Is it possible to color fill cells in a column of "Dates" with the conditions; red fill if date repeats less than 3 times, green fill if date repeates 3 or more times?
Thanks in advance if anyone can help!!
5/15/2014 red fill
5/15/2014 red fill
5/16/2014 green fill
5/16/2014 green fill
5/16/2014 green fill
5/16/2014 green fill
5/17/2014 green fill
5/17/2014 green fill
5/17/2014 green fill
5/18/2014 red fill
5/18/2014 red fill ...and so on...
Last edited by long8484; 06-02-2014 at 01:40 PM.
Assuming dates are in A2:A100
Select A2:A100 > Conditional Formatting >Using Formula (can't be more specific as you didn't say what version of Excel)
=COUNTIF($A$2:$A$100,A2)>=3 Format as red
similar for <3 format as green
Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Using Excel version 2007.
Thank you.
That works!! What does the ,A2 in parenthesis do?
It's a relative reference so on row 2, it refers to A2, on row 3, A3 and so on.
=COUNTIF(A2:100, A2) counts all the cells from A2 to A100 which are equal to A2
then
=COUNTIF($A$2:$A$100,A2)>=3 if that number is => 3 then the cell is red.
So with each row, it counts how many times the value in that row appears
Hope that helps.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks