countif question

[andonny]

Hi,
I use the countif function and it tells me that in text cells those two are the same. The result is two.
Why is that.

30017
000000000000030017

Thank you for your help
Andonny

 

[RalphA]

Excel views 30017 and 00000000030017 both as the number 30017, so, it counts two.

If you use "real" text, such as a10017 and a0000010017, it will see this as two, distinct texts.

 

[Peter_SSs]

andonny

You have the answer as to why. Here is one alternative if it is of any use.

Column A is formatted as text.
B1: =SUMPRODUCT(--(A1:A10=A1))
or
B2: =SUMPRODUCT(--(A1:A10="3017"))

Mr Excel.xls
	A	B	C	D
1	3017	3
2	0003017	3
3	569
4	3017
5	89
6	03017
7	1
8	256
9	3017
10	00003017
11
Sheet1