Google-spreadsheet: Error when trying to highlight all rows where values in Columns A and C are duplicates

Question

I put the Conditional Formatting "=AND(COUNTIF(A:A,A1)>1,COUNTIF(C:C,C1)>1)" on columns A through C. I did this with the goal of highlighting all rows where both the value in Column A and the Value in Column C are identical to another row in the spreadsheet. What is wrong with my logic, and why doesn't it work? It highlights things without any pattern that I can discern.

Answer 1

Base the CFR on,

=AND(COUNTIFS(A:A, A1, C:C, C1)>1)

Your formula didn't work because it was saying If A1 is in any row in column A more than once and C1 is in any row in column C more than once then true. In short, there was no commitment to the duplicates being in the same row.

_{Before you ask about the AND in a comment, it's just my formula writing style. The formula returns the same result without the AND(...) wrapper.}

Answer 2

Different interpretation. Insert a new ColumnA and enter in A1:

=ArrayFormula(B1:B&"|"&D1:D)

Select all cells, clear any existing CF rules and Format - Conditional formatting..., Custom formula is and:

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

Select formatting of choice and Done.