So I am building a spreadsheet and everything was working fine but then I kept tinkering with it and making it more intuitive (and less work on the user) and ran into a snag. What I am trying to do would really be best for a database but I digress.

First I will set the stage:

Column A | Column B | Column C
   01    |    D1    |  Name
   01    |    P1    |  Name
   03    |    D1    |  Name
   03    |    D1    |  Name

So this is in a nutshell what I have. Name is included but not relevant really. I know that if I do

=countif(A:A, A2)>1

in the conditional formatting, all 4 rows will be red (duplicate). This is cause there are two 01s and 03s. But in the realm of what I am doing this is not a duplicate. It should only be red if there are more than one duplicate matching in A Column containing the same B Column data. So row 1 and 2 are not duplicates but 3 and 4 are cause they both have D1 in the Column B and match in Column A.

I am not entirely sure what I should even look for here but I have spent the past 3 hours looking. Can someone here help me out on this? Thanks in advance.

Note 1: I have tried something like this but it works but fails my purpose. It returns true because there is in fact more than 1 D1 in column B as there should be. But it doesn't take into consideration I only care about exactly matching column A and B marking red.

=AND(countif(A:A, A2)>1, countif(B:B, B2)>1)

you need to use COUNTIFS:

  • EU: =COUNTIFS($A:$A; $A1; $B:$B; $B1)>1
  • US: =COUNTIFS($A:$A, $A1, $B:$B, $B1)>1


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.