Google Sheets - Color by Number of Entries

I had a spreadsheet collecting a long list of dates, and next to it a calendar I manually entered. I wanted to color cells on the calendar, so that the color indicated the Number of dates in a separate list that fell on that date. I wanted green if it was 1 or 2, Yellow if it was 3 or 4, and Red if it was 5.

It took me too long to figure it out, but here’s how you do it:

Create a “Conditional Formatting" with “Custom Formula", like so:

=IF(COUNT(FILTER($L$1:$L$200, $L$1:$L$200=DATE(2016,8,A3) ))<5, if(COUNT(FILTER($L$1:$L$200, $L$1:$L$200=DATE(2016,8,A3) ))>2, 1,0),0)

L1:L200 - The Column of dates.

A3 - The top-left cell containing the calendar (of August 2016 in this case)

This is the comparison for the 3 and 4 case, just change the <5 and >2 to whatever you need.

Hope it helps!

Next step is to get this to work for multiple columns of dates.

comments powered by Disqus