3

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?

EDIT:

This is a link to a demo sheet as requested

Improve this question
2
  • @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.
    – marikamitsos
    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
    Sep 2, 2018 at 2:06

1 Answer 1

Reset to default
2

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

Improve this answer
2
  • 1
    That extra $ did the trick.
    – marikamitsos
    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.
    – marikamitsos
    Sep 2, 2018 at 15:46

Your Answer

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

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