Dependant Multi Conditional Formatting

Question

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)

Answer 1

you need to use COUNTIFS:

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