CountIF Code slowing down sheet.

[Kiloelectronvolt]

Hi All!!

I am running this CountIf formula to try and detect any duplicate entries. IF columns B, C, and E are all the same on any rows above, I want it to turn the text RED to let me know that row has been entered above and is a duplicate.

However, this conditional formatting code has single handidly taken my spreadsheet down to a crawl. (unusable) -- The second I remove this formatting, the sheet works fine! is there a better way to do this? Thank you!!

=COUNTIFS($B:$B,$B1,$C:$C,C1,$E:$E,E1)>1

 

[Fluff]

Limit the ranges being used in the formula & also in the "Applies to" range

 

[Kiloelectronvolt]

Limit the ranges being used in the formula & also in the "Applies to" range

I used this and it seems to be working much better. However, if I enter a dupliacte row, it only turns all the above rows red, and not the row I just typed in. thank you!


=COUNTIFS($B1:$B2000,$B1,$C1:$C2000,C1,$E1:$E2000,E1)>1

 

[Fluff]

Try anchoring the ranges
=COUNTIFS($B$1:$B$2000,$B1,$C$1:$C$2000,$C1,$E$1:$E$2000,$E1)>1

 

[Kiloelectronvolt]

Fluff! Thank you!!!!!!!!!!!!!!!!!!

This is a very important document to me and this function was necessary. I appreciate you and this message board greatly. I spend hours trying on my own, but when I get stuck you all always have the answers.

 

[Fluff]

Glad to help & thanks for the feedback