Countif example
 Share
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

 
$
%
123
 
 
 
 
 
 
 
 
 
ABCDEFGHIJ
1
Item
Single column
(possible to mark row)
Item
Multiple columns
Notes
2
KiwisKiwisPearsKiwisThe occurrences where there's a problem have been marked red.
3
LemonsLemonsPearsApples
4
ApplesApplesPearsCiderIt seems that the check works from the top left to the bottom right, cell by cell instead of column by column.
Which means that if an item has not been found in a column/row to the left/top, then it won't be marked as a duplicate
5
OrangesOrangesPearsMango
6
LemonsLemonsMangoApples
7
ApplesApplesApplesMango
8
BananasBananasApplesPears
9
ApplesApplesApplesBananasPlease leave the sets on the left as they are, the bottom two are for testing.
10
MelonsMelonsBananas
Strawberries
11
GrapesGrapesCiderCider
12
Apples234ApplesCiderStrawberries
13
StrawberriesStrawberriesBananasGrapes
14
ApplesApplesBananasLemons
15
LemonsLemonsBananasBananasformula is: =COUNTIF($F$2:F2,F2)>1
16
17
18
19
Item
Multiple columns
for testingItem
Multiple columns
for testingThese sets has been added so anyone can test with them.
20
KiwisPearsKiwisKiwisPearsKiwis
21
LemonsPearsApplesLemonsPearsApples
22
ApplesPearsCiderApplesPearsCider
23
OrangesPearsMangoOrangesPearsMango
24
LemonsMangoApplesLemonsMangoApples
25
ApplesApplesMangoApplesApplesMango
26
BananasApplesPearsBananasApplesPears
27
ApplesApplesBananasApplesApplesBananas
28
MelonsBananas
Strawberries
MelonsBananas
Strawberries
29
GrapesCiderCiderGrapesCiderCider
30
ApplesCiderStrawberriesApplesCiderStrawberries
31
StrawberriesBananasGrapesStrawberriesBananasGrapes
32
ApplesBananasLemonsApplesBananasLemons
33
LemonsBananasBananasLemonsBananasBananasformula is: =(IF(COLUMNS($F20:F20)>1,COUNTIF(E$20:$F$33,F20),0)+COUNTIF(F$20:F20,F20))>1
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
Loading...