Why COUNTIF() returns zero in excel?

[Noni]

I've two columns of addresses (2022 customer address, and 2023 customer address) on the same sheet. I want to check if a 2023 address in column D is of an existing or new customer (comparing it with column C). I also aim to find out if the 2022 customer is no longer a customer in 2023.

=COUNTIF($C$2:$C$256,D2)
with the above formula, I'm checking if D2 exists in C2:C256 but I'm getting zero results though I know that D2(address) does exist in the range C2:C256. please help!!!!

 

[gargamalebarbosa]

Could you share screenshot etc. ?

 

[Noni]

Could you share screenshot etc. ?

below is an example. some address do have / and , in it. Hope below table makes sense.

2023 address	2022 Address
215/20B ABCD XYZ 6097	5C, 256 New Drive FGH 6093
167 XYZ Road ABC 6098	345 ABC Road OIU 1234
5C, 256 New Drive FGH 6093	1267 kjh/asd Cres OLM 1234
	1209 jhgf Rd OLM 1234
	167 XYZ Road ABC 6098

 

[Peter_SSs]

Welcome to the MrExcel board!

So, what is wrong with your formula here used with your sample data?
(BTW, you should investigate XL2BB for providing better/neater sample data )

22 08 28.xlsm
	C	D	E
1	2023 address	2022 Address
2	215/20B ABCD XYZ 6097	5C, 256 New Drive FGH 6093	1
3	167 XYZ Road ABC 6098	345 ABC Road OIU 1234	0
4	5C, 256 New Drive FGH 6093	1267 kjh/asd Cres OLM 1234	0
5		1209 jhgf Rd OLM 1234	0
6		167 XYZ Road ABC 6098	1
COUNTIF

Cell Formulas
Range	Formula
E2:E6	E2	=COUNTIF($C$2:$C$256,D2)

 

[Noni]

Welcome to the MrExcel board!

So, what is wrong with your formula here used with your sample data?
(BTW, you should investigate XL2BB for providing better/neater sample data )

22 08 28.xlsm
	C	D	E
1	2023 address	2022 Address
2	215/20B ABCD XYZ 6097	5C, 256 New Drive FGH 6093	1
3	167 XYZ Road ABC 6098	345 ABC Road OIU 1234	0
4	5C, 256 New Drive FGH 6093	1267 kjh/asd Cres OLM 1234	0
5		1209 jhgf Rd OLM 1234	0
6		167 XYZ Road ABC 6098	1
COUNTIF

Cell Formulas
Range	Formula
E2:E6	E2	=COUNTIF($C$2:$C$256,D2)

My formular eturns zero

 

[shift-del]

Hi

Check for extra spaces before or after the text.

 

[Noni]

Hi

Check for extra spaces before or after the text.

I ve checked that, still no luck

 

[shift-del]

And what is the result if you compare two cells that are supposed to be identical, like C3=D6?

 

[Noni]

And what is the result if you compare two cells that are supposed to be identical, like C3=D6?

If identical then should return 1 , otherwise 0

 

[shift-del]

No. It should return TRUE or FALSE.