countif question

andonny

Board Regular
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

Andonny

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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.

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
ABCD
130173
200030173
3569
43017
589
603017
71
8256
93017
1000003017
11
Sheet1

Replies
1
Views
89
Replies
3
Views
255
Replies
5
Views
290
Replies
1
Views
269
Replies
2
Views
144

1,196,073
Messages
6,013,269
Members
441,758
Latest member
Abren

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back