Conditional Format not acting as expected.

[MiniFav]

I have the following.

Hard time template idea.xlsx
	B	C
33	Pos test (dupes)	Other field test
34	1	1
35	1	2
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B34:C35	Expression	=IF(COUNTIF($B$34:$B$35,B34)>1,IF(COUNTIFS($B$34:$B$35,B34,$C$34:$C$35,"<>")>=2,TRUE,FALSE),FALSE)	text	NO

I would expect each cell to be highlighted, however C35 does not seem to be affected.

The theory behind this is Column B could have duplicate entries,
if there is duplicates then I want to check column C,
If Column C has a different data (not duplicate) then to highlight to indicate an issue.

I have a feeling I am using COUNTIFS wrong and should probably be mixing it with some match and index in some way.
Just looking for some fresh eyes to point me in the right direction.

 

[awoohaw]

What does the second IF criteria mean in the second countifs?
IF(COUNTIFS($B$34:$B$35,B34,$C$34:$C$35,"<>")>=2

 

[MiniFav]

What does the second IF criteria mean in the second countifs?
IF(COUNTIFS($B$34:$B$35,B34,$C$34:$C$35,"<>")>=2

I was looking for rows which have the same column B then have any character in Column C and if its greater than two instances to highlight it to me. If its just one instance then thats fine.

I have made a work around.


Everything I want highlighted is now highlighted.

Hard time template idea.xlsx
	A	B	C
33	Check MAC	Pos test (dupes)	Other field test
34	DIFFERENCE	1	b
35	DIFFERENCE	1	a
Sheet1

Cell Formulas
Range	Formula
A34:A35	A34	=IF(COUNTIFS($B$34:$B$35,B34,$C$34:$C$35,"<>")>1,IF(COUNTIF($C$34:$C$35,VLOOKUP(B34,$B$34:$C$35,2,0))=1,"DIFFERENCE",FALSE))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B35:C35	Expression	=IF($D35=TRUE,TRUE,FALSE)	text	NO
B34:C35	Expression	=IF($A34="DIFFERENCE",TRUE,FALSE)	text	NO

Then if there is just one instance its cleared.

Hard time template idea.xlsx
	A	B	C
33	Check MAC	Pos test (dupes)	Other field test
34	FALSE	1	b
35	FALSE	1
Sheet1

Cell Formulas
Range	Formula
A34:A35	A34	=IF(COUNTIFS($B$34:$B$35,B34,$C$34:$C$35,"<>")>1,IF(COUNTIF($C$34:$C$35,VLOOKUP(B34,$B$34:$C$35,2,0))=1,"DIFFERENCE",FALSE))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B35:C35	Expression	=IF($D35=TRUE,TRUE,FALSE)	text	NO
B34:C35	Expression	=IF($A34="DIFFERENCE",TRUE,FALSE)	text	NO