I have the following table
Col A Col B Col C [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 192"]
[TD="width: 64, bgcolor: transparent"]I[/TD]
[TD="width: 64, bgcolor: transparent"]Dog[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent, align: center"]FALSE[/TD]
[TD="bgcolor: transparent"]I[/TD]
[TD="bgcolor: transparent"]Cat[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[TD="bgcolor: transparent"]I[/TD]
[TD="bgcolor: transparent"]Mouse[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[TD="bgcolor: transparent"]E[/TD]
[TD="bgcolor: transparent"]Nose[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[TD="bgcolor: transparent"]E[/TD]
[TD="bgcolor: transparent"]Mouse[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"]Rats[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"]Pigs[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"]Cows[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"]Mouse[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[/TABLE]
in column C I have the formula
I want to check if column B is duplicated, but only if Col A <> "C"
in the table above, mouse appears more than once.
in the countifs formula I did not want entries that have a C in Column A
True should only show up for I and E entries that are a duplicate and do not have a C in column A.
I have looked at this for many hours and am stumped.
if I read the countifs formula description correctly, the above formula counts B1 in b1:b9 and a1:a9 counts <>C*
What am I missing
the results should be:
Col A Col B Col C [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 192"]
[TD="width: 64, bgcolor: transparent"]I[/TD]
[TD="width: 64, bgcolor: transparent"]Dog[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent, align: center"]FALSE[/TD]
[TD="bgcolor: transparent"]I[/TD]
[TD="bgcolor: transparent"]Cat[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[TD="bgcolor: transparent"]I[/TD]
[TD="bgcolor: transparent"]Mouse[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[TD="bgcolor: transparent"]E[/TD]
[TD="bgcolor: transparent"]Nose[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[TD="bgcolor: transparent"]E[/TD]
[TD="bgcolor: transparent"]Mouse[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]TRUE[/TD]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"]Rats[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"]Pigs[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"]Cows[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"]Mouse[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, align: center"]FALSE[/TD]
[/TABLE]
I have attached a spreadsheet.