Countif not taking entire cell into account.

I have a VERY long list of data, but as an example:

Please Login or Register  to view this content.

Countif is treating all of these as identical, so I am getting a count of 1953 records total that look like the above, but are being treated by Countif as identical enough to count.

Is there a degree of accuracy in Countif where it only identifies up to a certain number of characters? If so, how else can I find if a record is located in a different grouping?

=sumproduct(--($a$1:$a$34=a1))

Hi,

COUNTIF has the (sometimes useful, sometimes not) built-in feature that it converts data to numericals where possible.

Use SUMPRODUCT, which can differentiate between the two data types:

=SUMPRODUCT(0+(A1:A34=A1))

Regards

It seems like these numbers are too big for formula to evaluate them. What I did is I used another column and part of the string with formula:

=RIGHT(A2,17)


and then I used formula =COUNTIF($B$2:B2,B2) to evaluate.

Originally Posted by Phixer

These are all text fields. I am looking for non-unique values in the list.

The problem is that the first 16 digits are not the same in all the fields, it is just that in that example of the 1953 records pegged identical, none of them were beyond that number of digits.

Looks like I will have to write some VB code to solve this. Thanks.

?? Did you see/try the solutions posted by me and shg?

Regards

	a	b	c
1	60910708130400000010077738000306032040199924201059330253370	1	b14: =sumproduct(--($a$1:$a$34=a14))
2	60910708130400000010077738000306033130199913994920285764270	1
3	60910708130400000010077738000306032040199913420043207000670	2
4	60910708130400000010077738000306032040199913420043207000670	2
5	60910708130400000010077738000306030200099913420071042700770	11
6	60910708130400000010077738000306030200099913420071042700770	11
7	60910708130400000010077738000306030200099913420071042700770	11
8	60910708130400000010077738000306030200099913420071042700770	11
9	60910708130400000010077738000306030200099913420071042700770	11
10	60910708130400000010077738000306030200099913420071042700770	11
11	60910708130400000010077738000306033130199913290020078209770	2
12	60910708130400000010077738000306033130199913420071042700770	10
13	60910708130400000010077738000306033130199913420071042700770	10
14	60910708130400000010077738000306030200099913420071042700770	11
15	60910708130400000010077738000306030200099913420071042700770	11
16	60910708130400000010077738000306030200099913420071042700770	11
17	60910708130400000010077738000306030200099913420071042700770	11
18	60910708130400000010077738000306030200099913420071042700770	11
19	60910708130400000010077738000306033130199913420071042700770	10
20	60910708130400000010077738000306033130199913420071042700770	10
21	60910708130400000010077738000306033130199913420071042700770	10
22	60910708130400000010077738000306033130199913420071042700770	10
23	60910708130400000010077738000306033130199924204520189706870	1
24	60910708130400000010528138000306032040199944200023033440070	1
25	60910708130400000010077738000306033130199913420071042700770	10
26	60910708130400000010077738000306033130199913420071042700770	10
27	60910708130400000010077738000306030200099913994959308058170	4
28	60910708130400000010077738000306030200099913994959308058170	4
29	60910708130400000010077738000306032040199924201020069955470	1
30	60910708130400000010077738000306030200099913994959308058170	4
31	60910708130400000010077738000306030200099913994959308058170	4
32	60910708130400000010077738000306033130199913420071042700770	10
33	60910708130400000010077738000306033130199913420071042700770	10
34	60910708130400000010077738000306033130199913290020078209770	2

Interested in this for a variety of reasons... How can you copy & paste these long strings as text??

Format the cells as text beforehand, paste with destination formatting.

Originally Posted by Glenn Kennedy

Interested in this for a variety of reasons... How can you copy & paste these long strings as text??

Format your receiving cells as text first. Then make sure you use "Match Destination Formatting" when pasting.

Regards

You could post a portion of the actual workbook, and someone can add the formula ...

I'm still not sure what SUMPRODUCT is actually telling me. It ISN'T telling me the number of unique values in the list (in the example above, there are 10). There are four 2's in the example posted by shg. However, these four are made up of two unique values, each repeated once.

The number of unique values is given by:

Formula:

Please Login or Register  to view this content.

... and FINALLY... this will return the identity of the unique values (Range in A1:A35, this equation in B2, B1 must be blank and enter as an array).

Formula:

Please Login or Register  to view this content.

I haven't a bloody clue HOW it works, but it does.