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?
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