Mar 29 2019 07:09 AM
Hi team,
I'm using sum.if formula on two different tables.
The criteria to sum up amounts is the list of numbers here below.
5806524220010000003340946
5806524220010000016288417
5806524220010000058829800
5806524220010000063264583
5806524220010000102078436
5806524220010000129209717
5806524220010000130237426
5806524220010000131163634
5806524220010000139123086
5806524220010000151785368
5806524220010000346045207
5806524220010000513763826
5806524220010000639396310
5806524220010000849782043
5806524220010001236172501
5806524220010001395084367
5806524220010001556090273
5806524220010001913121498
5806524220010004397564380
5806524220010000129213425
5806524220010000146617302
5806524220010001819529873
5806524220010000121349204
5806524220010003209284922
5806524220010000226067507
5806524220010000421729083
5806524220010001348673320
They are different but all this keys returned all the same result (it seems that key is considered until 58065242200100 and last numbers of each keys are lost...so all of them appear to be the same)
Please can you help me to understand the problem?
thanks and regards
r
Mar 29 2019 07:20 AM
@rc14354 , yes, Excel recognizes only first 15 digits and drops the rest when you do calculations with such values as with numbers. As workaround, if you leading digits are always the same, you may split that column on two, first 10-15 digits and the rest (Data->Text to Columns) and operate with the second column.
Mar 29 2019 07:29 AM
Thanks for your prompt reply.
Unfortunately leading numbers are not always the same.
Nevertheless in the REMARKS in https://support.office.com/en-us/article/sumif-function-169b8c99-c05c-4483-a712-1697a653039b
is said that "The SUMIF function returns incorrect results when you use it to match strings longer than 255 characters or to the string #VALUE!."
So I thought that the problem was not lenght of the criteira string.
Thanks a lot
rinaldo
Mar 29 2019 07:39 AM
@rc14354 , you have no one error on your screenshot. And what do you exactly do, you convert texts in first column to values and sum them based on criteria, or you use them as criteria?
Mar 29 2019 07:42 AM
I do not convert first column in text but I use them directly as criteria
Mar 29 2019 07:51 AM
SolutionMar 29 2019 08:01 AM
Mar 29 2019 08:16 AM
Oh, yes, I totally forgot about magic of the CHAR(173). Thank you, @Detlef Lewin
Mar 29 2019 10:43 AM
Mar 29 2019 12:37 PM
Ah! The discoverer herself.
Solving an eleven year old problem which annoyed almost every Excel user is always worth mentioning.
Mar 29 2019 07:51 AM
Solution