IF COUNTIF function not working correctly

[Helloitsme]

Hello all, unfortunately I can't get XL2BB program to work, so I'll try to describe my issue. It is likely an easy fix but I just can't seem to wrap my head around it.

Line	Value		Line	Value	Unique?
FIBER DSL	029860000		COAX I/E-KUNDE	028020000	X
TASTET AF:	076604000		FIBER DSL	029860000	X
M104895	076604000		TASTET AF:	076604000

Basically, I'm looking at the "Unique?" column. In this column I have used the following function:
=IF(COUNTIF(B:B;E2)>1;"";"X")

I want the column to fill in an X, when it detects a unique value that is not present in the left table. However, the "Fiber DSL" value is clearly visible in the left table, and I don't understand why an X is returned for this one.

Any help is greatly appreciated!

 

[RoryA]

Your formula is counting whether the value in E2 appears more than once in column B, not whether it appears there at all. If you want the latter, then you should use >0 rather than >1

 

[Peter_SSs]

unfortunately I can't get XL2BB program to work,

See if this this helps (don't focus on the thread title, but what is written in the posts). Otherwise tell us more about exactly what goes wrong when you try to use it.

 

[Helloitsme]

Your formula is counting whether the value in E2 appears more than once in column B, not whether it appears there at all. If you want the latter, then you should use >0 rather than >1

Thanks for the reply, this was also my assumption and I have tried it, however, when I put the >0 instead of >1, the X doesnt appear at all, which is incorrect as the first line on the right table is not present on the left table.

 

[Helloitsme]

See if this this helps (don't focus on the thread title, but what is written in the posts). Otherwise tell us more about exactly what goes wrong when you try to use it.

I read through it but it's not the scenario I'm facing. Whenever I try opening the XL2BB add in, it comes with the following error:

I believe this might be because I'm on my work station, as I can't seem to remove the Protected View.

 

[Peter_SSs]

however, when I put the >0 instead of >1, the X doesnt appear at all

Doesn't it? It does for me.

24 11 21.xlsm
	A	B	C	D	E	F
1	Line	Value		Line	Value	Unique?
2	FIBER DSL	29860000		COAX I/E-KUNDE	28020000	X
3	TASTET AF:	76604000		FIBER DSL	29860000
4	M104895	76604000		TASTET AF:	76604000
5
Sheet3

Cell Formulas
Range	Formula
F2:F4	F2	=IF(COUNTIF(B:B,E2)>0,"","X")

Whenever I try opening the XL2BB add in, it comes with the following error:

Did you try what was suggested in that thread? That is ..
Make sure the add-in file is unblocked and that it is in a trusted location. Both of these are covered in post 3 of that thread.

It worked for this person reporting the same message as you and also this one.

 

[Helloitsme]

Doesn't it? It does for me.

24 11 21.xlsm
	A	B	C	D	E	F
1	Line	Value		Line	Value	Unique?
2	FIBER DSL	29860000		COAX I/E-KUNDE	28020000	X
3	TASTET AF:	76604000		FIBER DSL	29860000
4	M104895	76604000		TASTET AF:	76604000
5
Sheet3

Cell Formulas
Range	Formula
F2:F4	F2	=IF(COUNTIF(B:B,E2)>0,"","X")

Did you try what was suggested in that thread? That is ..
Make sure the add-in file is unblocked and that it is in a trusted location. Both of these are covered in post 3 of that thread.

It worked for this person reporting the same message as you and also this one.

Hello Peter, sorry I might just be a little slow today. Got it to work again thanks!

This is the table that I use. Do you think the issue could be caused as I'm using functions to return text to the cells? This table is with the >0 function:

Copy of sp_double_ordre_sample_to_validate.xlsx
	A	B	C	D	E	F
1	Line	Value		Line	Value	Unique?
2	FIBER DSL	029860000		COAX I/E-KUNDE	028020000
3	TASTET AF:	076604000		FIBER DSL	029860000
4	M104895	076604000		TASTET AF:	076604000
Sheet1

Cell Formulas
Range	Formula
A2:A4	A2	=IFERROR(LEFT(AA1,LEN(AA1)-9),"")
B2:B4	B2	=IFERROR(RIGHT(AA1,9),"")
D2:D4	D2	=IFERROR(LEFT(AC1,LEN(AC1)-9),"")
E2:E4	E2	=IFERROR(RIGHT(AC1,9),"")
F2:F4	F2	=IF(COUNTIF(B:B,E2)>0,"","X")

 

[Peter_SSs]

Glad to see that you got XL2BB going.

Do you think the issue could be caused as I'm using functions to return text to the cells?

No. I think that those values in E2:E4 must be in column B somewhere. Also, it appears that you have two formal excel tables so you could use structured references and avoid the whole column references. Something like I have in column G below. You could also add a new column to the right hand table and populate it with the formula shown. It should tell you what row in column B the number in column E is found.

Check table names in the formulas.

24 11 21.xlsm
	A	B	C	D	E	F	G
1	Line	Value		Line	Value	Unique?	Where?
2	FIBER DSL	29860000		COAX I/E-KUNDE	28020000	X	#N/A
3	TASTET AF:	76604000		FIBER DSL	29860000		2
4	M104895	76604000		TASTET AF:	76604000		3
5
6
Sheet4

Cell Formulas
Range	Formula
F2:F4	F2	=IF(COUNTIF(Table1[Value],[@Value])>0,"","X")
G2:G4	G2	=MATCH([@Value],Table1[Value],0)+1

 

[Helloitsme]

Glad to see that you got XL2BB going.


No. I think that those values in E2:E4 must be in column B somewhere. Also, it appears that you have two formal excel tables so you could use structured references and avoid the whole column references. Something like I have in column G below. You could also add a new column to the right hand table and populate it with the formula shown. It should tell you what row in column B the number in column E is found.

Check table names in the formulas.

24 11 21.xlsm
	A	B	C	D	E	F	G
1	Line	Value		Line	Value	Unique?	Where?
2	FIBER DSL	29860000		COAX I/E-KUNDE	28020000	X	#N/A
3	TASTET AF:	76604000		FIBER DSL	29860000		2
4	M104895	76604000		TASTET AF:	76604000		3
5
6
Sheet4

Cell Formulas
Range	Formula
F2:F4	F2	=IF(COUNTIF(Table1[Value],[@Value])>0,"","X")
G2:G4	G2	=MATCH([@Value],Table1[Value],0)+1

Hello Peter, thanks again, I populated the G column and you're absolutely correct. However, there's still something wrong with the COUNTIF function. Please see below:

Copy of sp_double_ordre_sample_to_validate.xlsx
	D	E	F	G
52	TRACK&TRACE NR:	076603700		34
53		76603700		#N/A
Sheet1

Cell Formulas
Range	Formula
D52:D53	D52	=IFERROR(LEFT(AC51,LEN(AC51)-9),"")
E52:E53	E52	=IFERROR(RIGHT(AC51,9),"")
F52:F53	F52	=IF(COUNTIF(B:B,E52)>0,"","X")
G52:G53	G52	=MATCH([@Value],B:B,0)+1

 

[Peter_SSs]

I can't really tell anything from that as I can't see where or if 76603700 is in column B. Can you make up a small sample sheet where that circumstance occurs, perhaps by copying the worksheet and deleting most of the rows but keeping the problem, and posting all of it with XL2BB?