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. Sep 2 '18 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
    Sep 2 '18 at 2:06

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. Sep 2 '18 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. Sep 2 '18 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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