Clarification on how =countif(A:A,A1)>0 works as a conditional formatting rule

2
votes

Everything I've read says that the countif function works as =countif(range, criteria). It seems to work that way in the sheet itself, but I'm confused about how it works as as a custom formula in Conditional formatting.

I enter =countif(A:A, A1)>1 as a custom formula applied to Range A:A.

Assuming the following values are in column A:

5
10
23
54
6
65
10
65

The values 10 and 65 are highlighted, which is the outcome I want. But since I specified A1 as the criteria (which contains a value of 5), shouldn't all fields not be highlighted since there's only 1 field with a value of 5?

What am I missing?

1
google-sheetsconditional-formatting

1 Answers

1
votes

A1 in =COUNTIF(A:A, A1)>1 is not locked like A$1 so it iterates over the whole specified range. in other words it runs:

=COUNTIF(A:A, A1)>1

and then it continues like:

=COUNTIF(A:A, A1)>1
=COUNTIF(A:A, A2)>1
=COUNTIF(A:A, A3)>1
...

its an advantage of custom formulae that they can iterate ower the range