1

Many blog posts and educational sites tell us to use =countif($A$2:A2,A2)=1 in the selected range.

However, they don't explain it. And for me it doesn't work and since I can't understand it I can't debug it.

My understanding about Sheets is that $ is for absolute reference. Thus $A$2 means an absolute reference to the A2 cell. For me the formula becomes A2:A2 which is weird.

Can anyone please explain how this formula prevents the duplicates and what is the logic behind it? Thank you.

1 Answer 1

2

The range reference $A$2:A2 is using a mixed address mode where $A$2 is absolute and A2 is relative. When the formula is "dragged down" by copy-pasting cells or applying it to a range in a conditional formatting custom formula rule, the first part stays put and the second part adjusts, like this:

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

=countif($A$2:A3, A3) = 1

=countif($A$2:A4, A4) = 1

...and so on.

countif() will thus get the number of values that match the value in the current row, looking at the range that starts in A2 and ends in the current row of column A. If that count is 1, the formula gets true. If there are more than one copy of the value in the rows above and including the current row, the formula gets false.

See absolute and relative references.

Your Answer

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

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