CODEWITHSUNDEEP
excelconditional-formatting
Nick Freeman
Nick Freeman

Reputation: 19

Conditional Formatting count if rules not working correctly

I am creating an excel spreadsheet that need to note if the same number appears more than once then it goes yellow if twice or more it goes red. When i enter it the first time it should stay blank, the next time the same number is entered both should turn yellow, on the 3rd occassion and anymore accasions, all the numbers should turn red.

=COUNTIF(B3:B99,B3)>2

=COUNTIF(B3:B99,B3)>1

enter image description here

It does work to some extent however it does not seem to highlight them properly. The first one will display red the second on will be yellow and the third one will be white, they should all be red on the 2nd time entering the number see below

enter image description here

As you can see all the 77301 numbers should be red not 2 in red one in yellow and 1 blank. My top 3 rows are frozen would this cause the issue?

Upvotes: 0

Views: 511

Answers (1)

BigBen
BigBen

Reputation: 50008

You're close. Use absolute references here, at least on the row portion.

=COUNTIF(B$3:B$99,B3)>2

=COUNTIF(B$3:B$99,B3)>1

enter image description here

Upvotes: 2

Related Questions