An issue with CountIF and the original data in the fields

[mimih23]

Hello!
I cannot for the life of me figure out why I am receiving an error for a simple CountIF formula. I have a total of 12663 items in Column A with duplicates and all I want is to count the number of duplicates for each. It works great for the majority but there are a few that the formula is reading as duplicates. There are no extra spaces, commas, apostrophes, etc. within these cells. Any suggestions. Here is what the formula is yielding:


0D9988 SSL 4
0D9988 SSL 4
0D9988 SSL 4
0D9988 SSL 4
0D9989 SSL 1
0D9990 SSL 1
0D9996 SSL 1
0E0002 SSL 53
0E0009 SSL 53
0E0015 SSL 53
0E0015 SSL 53
0E0017 SSL 53
0E0018 SSL 53
0E0018 SSL 53
0E0022 SSL 53
0E0022 SSL 53
0E0024 SSL 53
0E0034 SSL 53
0E0038 SSL 53
0E0045 SSL 53
0E0045 SSL 53
0E0045 SSL 53
0E0045 SSL 53
0E0045 SSL 53
0E0045 SSL 53
0E0045 SSL 53
0E0045 SSL 53
0E0049 SSL 53
0E0052 SSL 53
0E0057 SSL 53
0E0074 SSL 53
0E0077 SSL 53
0E0084 SSL 53
0E0093 SSL 53
0E0132 SSL 53
0E0132 SSL 53
0E0157 SSL 53
0E0176 SSL 53
0E0179 SSL 53
0E0188 SSL 53
0E0193 SSL 53
0E0200 SSL 53
0E0202 SSL 53
0E0202 SSL 53
0E0207 SSL 53
0E0212 SSL 53
0E0217 SSL 53
0E0228 SSL 53
0E0233 SSL 53
0E0233 SSL 53
0E0253 SSL 53
0E0258 SSL 53
0E0269 SSL 53
0E0271 SSL 53
0E0277 SSL 53
0E0281 SSL 53
0E0284 SSL 53
0E0286 SSL 53
0E0287 SSL 53
0E0297 SSL 53
0E0312 SSL 1
0E0314 SSL 1

As you can see, the formula (=CountIF(A:A,A:A) is working for all except the 53 items which are obviously not the same. Let me know if you have suggestions. The error message appears on all 53 items stating that "the number in this cell is formatted as text or preceded by an apostrophe." All of the items are formatted as text. To change the format to general or number would corrupt they are all alphanumeric. You can see that 0E0312 nor 0D9988 is affected by this. Help!!

 

[JoeMo]

I cannot reproduce your issue for the "53" items when I simply copy them from your post and apply your formula. I get the expected result for each item in the list.

 

[seenfresh]

I also do not reproduce the error, however I used the following formula:

=COUNTIF(A:A,A1)

Instead of:

COUNTIF(A:A,A:A)

Assuming the data starts in cell A1, copy down

 

[mimih23]

Oh geez...what does that mean!??!
Well, thanks for trying. I'm guessing the original data that was pulled from the much larger database was already corrupt......no matter what I do to correct it, it will not yield. Any suggestions on deleting the formatting, etc. without deleting the data? Has anyone ever seen this happen before? I've retyped it, copied.....reformatted...nothing. (scratching head).

 

[mimih23]

Thanks Seenfresh, I attempted that formula as well and the same result of 53. It must be with the original data....but it just boggles me that I can't reformat it to work correctly....nevermind...it can't just be the original data.....I just tried keying the numbers in a different workbook and still an "error arrow" appears. It's something to do with the fact that they start with 0E00 to 0E02 I suppose... I hope that I can come to a resolution soon. I wonder if it has anything to do with using 2003 vs. 2007 etc. Tomorrow I will be using 2007 in the office.....

 

[AlphaFrog]

If 0E0002 is in one cell and SSL is in the adjacent cell, the value of 0E0002 can be interpreted by Excel as scientific notation. That means all 0E0nnn values will equal zero and when you try to count them you will count 53 zeros.

Read this...
Text or number converted to unintended number format in Excel

 

[mimih23]

I deleted the SSL items and no longer have a column between this group of data. However, the formula still yields 53. I even attempted to change the format using the Custom option below:

Typing a Value with Leading Zeros

Because leading zeros are insignificant digits, Microsoft Excel omits them when you type a number, such as 00023, in a cell. In this case, Microsoft Excel applies the General number format.

To type a value with a leading zero, you can use one of the methods described in the previous section to type the value as text, or you can use the following steps to create a custom number format that contains leading zeros.
Select the cell range that you want to format.
On the Format menu, click Cells. On the Number tab, click Custom.
In the Type box, type a zero for each digit in the number. For example, if the number has five digits, type five zeros.
Click OK.
If you import a text file or parse text, you can use this procedure after you import or parse the text. You do not need to preformat the entries as text. However, you must preformat numbers that are evaluated as dates, times, or scientific notation.

....and the formula still yields 53 for all 53 items. (shrugs)

 

[barry houdini]

AlphaFrog is spot on, although you have text values COUNTIF effectively converts these to numeric (if you have text-formatted numbers then COUNTIF will only read 15 significant digits). Try using SUMPRODUCT, i.e.

=SUMPRODUCT((A$1:A$1000=A1)+0)

 

[AlphaFrog]

Same as Berry's but different...
=SUMPRODUCT(--("A"&$A$1:$A$62="A"&A1))

 

[barry houdini]

Hello AlphaFrog,

so the idea is to concatenate a letter to the values to ensure they are text values?

That'll work, but I don't think it's necessary. SUMPRODUCT (unlike COUNTIF) won't see the values as numbers so they should be counted individually, even with the "standard" SUMPRODUCT version I suggested.