I have a google sheet list of membership fees per user and per year. Currently it only highlights rows if a user id is already in the list and thus duplicate. This is how the conditional formatting formula looks like.

=countif($A:$A,$A2)>1

However, it is possible that a user is listed twice or even three times because the fees belong to different years. In this case, everything would be fine and the row does not need to be highlighted.

So the goal is to highlight all the rows where there are duplicate users with only the same year.

The headings/columns of the list:

User id || first name || last name || year || Amount

I would be glad if someone could help me.

Thanks in advance!

CodePudding user response：

Use:

=COUNTIFS($A:$A,$A1,$D:$D,$D1)>1