For the range C2:Z100 we apply the following formula to find duplicate students' enrollments using conditional formatting:

=COUNTIFS($C:$C,C2 , $D:$D,D2)>1

(where column C is the last name and D is the first name e.g. John Smith)

Unfortunately, we only get single duplicate cells in column C highlighted.

But... We want to have the entire row highlighted.

We have tried different variations and suggestions from the site as here and here or here (as well as many others). Nothing seems to work for the entire row. Also tried with COUNTIF , =AND (COUNTIFS($C:$C,C2 , $D:$D, D2)>1), COUNTA.No luck.

On the other hand this formula =$B2="G" highlights an entire row

What do we do wrong?


This is a link to a demo sheet as requested

  • @user0 I know a sheet would help but students data are sensitive and would have to prepare a new one. On the other hand I believe the answer should be "quite simple". We are just stuck. Commented Sep 2, 2018 at 0:58
  • 1
    there is no need for the real sheet with real sensitive data... a mare dummy with faked data is more than enough. what matters is time - including sample data will save time for both sides (your "wait time" and our "time we spend recreating the whole thing from scratch and guessing for unknown variables omitted in question") and fasten up the answer you seek
    – user0
    Commented Sep 2, 2018 at 2:06

1 Answer 1


Conditional formatting is based on the single reference which is then multiplied on a given range. To assure reference is single you need to lock it down with $ symbol. In this particular case, you need to lock columns because conditions are static column-wise and keep them dynamic row-wise. Therefore:

US syntax: =COUNTIFS($C:$C, $C2, $D:$D, $D2)>1
EU syntax: =COUNTIFS($C:$C; $C2; $D:$D; $D2)>1

  • 1
    That extra $ did the trick. Commented Sep 2, 2018 at 3:07
  • As mentioned before your answer is to the point. At the same time I find the logic behind your revisions totally incomprehensible. Commented Sep 2, 2018 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.