Countif not counting duplicate values correctly

[welchs101]

Hi see enclosed file,

Please open the enclosed file.

I have 3 cols of data Num1, Num2 and Date.

i concatenate various combinations of Num1, Num2 and Date.

See col = ConCat_Cols_ABC.....see cell D5. If i want to find the number of duplicates for this entry "107888344610834202" see countif in col = Countif_ColD.
it indicates that there are two entries for "107888344610834202" but if you look at col = Chk_Cnts_For_"107888344610834202" where i do an if/then.....
the results indicate that there is only 1 entry for this value of "107888344610834202"

i did various other attempts at trying to figure htis out by concatenating other things........from what i can tell is if i have the date in the concate ...things dont work right.

can someone help me?

[AliGW]

Did you mean to post in the VBA section? If not, I shall move the thread for you.

A good sample workbook has just 10-20 rows of representative data (not 1000s!) that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[Glenn Kennedy]

Excel can only handle 15 significant figures. Your concatenated strings have 18

So. These tow:

107888344610834201
107888344610834202


are seen as being the same. So... use this instead:

=SUMPRODUCT(--($D$2:$D$12000=D2))

[AliGW]

There's no need to concatenate at all if you use COUNTIFS.

[Glenn Kennedy]

Ali, True... but who knows if some of the strings alone are not >15 characters!! and there my be a reason for wanting to conatenate them that we know nothing about!!

[AliGW]

Who knows?

[welchs101]

yes, i did not mean for this to go into programming section...sorry about that.

when you concatenate you can only concatenate up to 15 characters?


also, i did not understand your soln....can you explain further.....
i thought countifs was only for summing things

[welchs101]

so i think you're right.........but i was not trying ot really have a "number" for the concatenated string.......so to make sure the concatenate was a string i added "a" to the beginning and
then this gave me the correct counts.........thanks....

[Glenn Kennedy]

No you can concatenate >>15 characters. It's just that COUNTIF will try to convert the big string back into a number and will then only look at the first 15 characters. So these two:

107888344610834201
107888344610834202

are seen by COUNTIFS as being

107888344610834
107888344610834

which are the same. The advantage SUMPRODUCT has is that it will look at the entire string, be it 1 or 100 characters and llok for a match - returning TRUE or FALSE... whcich the -- converts into 1 or 0 and which SUMPRODUCT then adds up.


You're welcome.


It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.