Countif not working...

[bob ridolfo]

I am working on a spreadsheet with a text string using 22 characters. When i do a countif to check how many of the same values exist I am getting some weird results:

	a	b	countif	result
1	25600591850001905187	256005918500019051870010	=countif(B:B,B1)	6
2	25600591850001905187	256005918500019051870011	=countif(B:B,B2)	6
3	25600591850001905187	256005918500019051870012	=countif(B:B,B3)	6
4	25600591850001905187	256005918500019051870013	=countif(B:B,B4)	6
5	25600591850001905187	256005918500019051870013	=countif(B:B,B5)	6
6	25600591850001905187	256005918500019051870013	=countif(B:B,B6)	6

I don't understand the result - rows 1 - 3 should be 1 and rows 4-6 should be 3
Columns A&B are formatted as TEXT
Is there a limit to the number of characters you can use in a "countif" statement??

Thanks
bob

 

[Fluff]

Hi & welcome to MrExcel.
Countifs will "convert" text to numbers where possible try it like

Fluff.xlsm
	A	B	C
1	2.56E+19	256005918500019051870010	1
2	2.56E+19	256005918500019051870011	1
3	2.56E+19	256005918500019051870012	1
4	2.56E+19	256005918500019051870013	3
5	2.56E+19	256005918500019051870013	3
6	2.56E+19	256005918500019051870013	3
Sheet5

Cell Formulas
Range	Formula
C1:C6	C1	=SUM(IF($B$1:$B$10=B1,1,0))

 

[bob ridolfo]

Hi & welcome to MrExcel.
Countifs will "convert" text to numbers where possible try it like

Fluff.xlsm
	A	B	C
1	2.56E+19	256005918500019051870010	1
2	2.56E+19	256005918500019051870011	1
3	2.56E+19	256005918500019051870012	1
4	2.56E+19	256005918500019051870013	3
5	2.56E+19	256005918500019051870013	3
6	2.56E+19	256005918500019051870013	3
Sheet5

Cell Formulas
Range	Formula
C1:C6	C1	=SUM(IF($B$1:$B$10=B1,1,0))

Thank you - did not think to use a "sum(if)" versus countif but it works great.

 

[Fluff]

You're welcome & thanks for the feedback.