=countif($f$2:$f100, f100)<2

[rentonhighlands]

Members:
Can you explain in words what this formula is doing.
What I have is a list of values and in the same list there are duplicates. What the formula returns is always the first instance of the duplicate (true) then the rest (false).

But can you explain logically what the formula is doing?

=COUNTIF($F$2:$F100, F100)<2

 

[gaz_chops]

Hi,

It simply counts the number of occurrences of each value in Col F, the 1st occurrence will be less than 2, so True, any other occurrence will Greater than 1, so False.

Hth

Gaz

 

[rentonhighlands]

Yes that helps.
Would it work if it said F2:F100, F100 without the $ sign?

 

[gaz_chops]

No, if you look the 1st instance of the formula, it will say $F$2:$F2, then $F$2:$F3, this allows it to COUNT each instance it occurs. If you remove the $ then it will show F2:F2 then F3:F3, so each instance will be the 1st.

Gaz

 

[Aladin Akyurek]

=COUNTIF($F$2:$F100, F100)

counts how often F100 occurs in F2:F100, and

=COUNTIF($F$2:$F100, F100)<2

returns TRUE if the count is less than 2.