Counting and colour coding ranges of occurances

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

a have a list of employee ID numbers and need to count how many times they
occur withing certain ranges. For example: if one occurs between 5-10 times
I want all occurances highlighted in yellow, if it occurs between 10-15 times
I want all occurances highlighted in blue, and if it occurs more than 15
times I want all occurances highlighted in red. Any suggestions?
 
You can use conditional formatting using these formulas for the three
conditions.

=AND(COUNTIF($A$1:$A$30,A1)>5,COUNTIF($A$1:$A$30,A1)<11)
=AND(COUNTIF($A$1:$A$30,A1)>10,COUNTIF($A$1:$A$30,A1)<16)
=COUNTIF($A$1:$A$30,A1)>15

Regards,
Alan.
 
I still can't get it to work. I know it is obviously operator error but
still need some help. My data is in column C which grows longer every day.
Every conditional format I try recognizes the first condition but not the
second and third. For example a number may appear 12 times but it still
codes with the color from 5-10. What am I doing wrong?
 
Try using Conditional Formatting in C1 using 'Formula Is' with these three
formulas in the three conditions, then highlight the whole of column C and
go to Conditional Formatting again, OK
=AND(COUNTIF(C:C,C1)>5,COUNTIF(C:C,C1)<11)
=AND(COUNTIF(C:C,C1)>10,COUNTIF(C:C,C1)<16)
=COUNTIF(C:C,C1)>15
Regards,
Alan.
 
Back
Top