SOLVED

SUM.IF

%3CLINGO-SUB%20id%3D%22lingo-sub-390806%22%20slang%3D%22en-US%22%3ESUM.IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390806%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20team%2C%3C%2FP%3E%3CP%3EI'm%20using%20sum.if%20formula%20on%20two%20different%20tables.%3C%2FP%3E%3CP%3EThe%20criteria%20to%20sum%20up%20amounts%20is%20the%20list%20of%20numbers%20here%20below.%3C%2FP%3E%3CP%3E5806524220010000003340946%3C%2FP%3E%3CP%3E5806524220010000016288417%3C%2FP%3E%3CP%3E5806524220010000058829800%3C%2FP%3E%3CP%3E5806524220010000063264583%3C%2FP%3E%3CP%3E5806524220010000102078436%3C%2FP%3E%3CP%3E5806524220010000129209717%3C%2FP%3E%3CP%3E5806524220010000130237426%3C%2FP%3E%3CP%3E5806524220010000131163634%3C%2FP%3E%3CP%3E5806524220010000139123086%3C%2FP%3E%3CP%3E5806524220010000151785368%3C%2FP%3E%3CP%3E5806524220010000346045207%3C%2FP%3E%3CP%3E5806524220010000513763826%3C%2FP%3E%3CP%3E5806524220010000639396310%3C%2FP%3E%3CP%3E5806524220010000849782043%3C%2FP%3E%3CP%3E5806524220010001236172501%3C%2FP%3E%3CP%3E5806524220010001395084367%3C%2FP%3E%3CP%3E5806524220010001556090273%3C%2FP%3E%3CP%3E5806524220010001913121498%3C%2FP%3E%3CP%3E5806524220010004397564380%3C%2FP%3E%3CP%3E5806524220010000129213425%3C%2FP%3E%3CP%3E5806524220010000146617302%3C%2FP%3E%3CP%3E5806524220010001819529873%3C%2FP%3E%3CP%3E5806524220010000121349204%3C%2FP%3E%3CP%3E5806524220010003209284922%3C%2FP%3E%3CP%3E5806524220010000226067507%3C%2FP%3E%3CP%3E5806524220010000421729083%3C%2FP%3E%3CP%3E5806524220010001348673320%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThey%20are%20different%20but%20all%20this%20keys%20returned%20all%20the%20same%20result%20(it%20seems%20that%20key%20is%20considered%20until%2058065242200100%20and%20last%20numbers%20of%20each%20keys%20are%20lost...so%20all%20of%20them%20appear%20to%20be%20the%20same)%3C%2FP%3E%3CP%3EPlease%20can%20you%20help%20me%20to%20understand%20the%20problem%3F%3C%2FP%3E%3CP%3Ethanks%20and%20regards%3C%2FP%3E%3CP%3Er%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-390806%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391077%22%20slang%3D%22en-US%22%3ERe%3A%20SUM.IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391077%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAh!%20The%20discoverer%20herself.%3C%2FP%3E%3CP%3ESolving%20an%20eleven%20year%20old%20problem%20which%20annoyed%20almost%20every%20Excel%20user%20is%20always%20worth%20mentioning.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390990%22%20slang%3D%22en-US%22%3ERe%3A%20SUM.IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390990%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3EHey%20just%20noticed%20this%20link!%20Indeed%20for%20numeric%20text%20you%20can%20use%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DSUMIF(range%2C%20CHAR(173)%26amp%3Bcriteria%2C%20%5Bsum_range%5D)%3CBR%20%2F%3E%3CBR%20%2F%3EGlad%20others%20have%20picked%20up%20on%20this%20and%20it's%20been%20put%20to%20good%20use%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390877%22%20slang%3D%22en-US%22%3ERe%3A%20SUM.IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390877%22%20slang%3D%22en-US%22%3E%3CP%3EOh%2C%20yes%2C%20I%20totally%20forgot%20about%20magic%20of%20the%20CHAR(173).%20Thank%20you%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390867%22%20slang%3D%22en-US%22%3ERe%3A%20SUM.IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390867%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F310545%22%20target%3D%22_blank%22%3E%40rc14354%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETime%20to%20bring%20up%20this%20little%20piece%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fnewtonexcelbach.com%2F2017%2F12%2F22%2Fthe-countif-bug-and-how-to-avoid-it%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3EThe%20Countif%20bug%20(and%20how%20to%20avoid%20it)%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390843%22%20slang%3D%22en-US%22%3ERe%3A%20SUM.IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390843%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F310545%22%20target%3D%22_blank%22%3E%40rc14354%3C%2FA%3E%26nbsp%3B%2C%20I%20see.%20The%20workaround%20is%20to%20use%20SUMRODUCT%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20783px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F100530i1F27D18447FA22C8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPlease%20check%20in%20attached%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390838%22%20slang%3D%22en-US%22%3ERe%3A%20SUM.IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390838%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20do%20not%20convert%20first%20column%20in%20text%20but%20I%20use%20them%20directly%20as%20criteria%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390833%22%20slang%3D%22en-US%22%3ERe%3A%20SUM.IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390833%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F310545%22%20target%3D%22_blank%22%3E%40rc14354%3C%2FA%3E%26nbsp%3B%2C%20you%20have%20no%20one%20error%20on%20your%20screenshot.%20And%20what%20do%20you%20exactly%20do%2C%20you%20convert%20texts%20in%20first%20column%20to%20values%20and%20sum%20them%20based%20on%20criteria%2C%20or%20you%20use%20them%20as%20criteria%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390824%22%20slang%3D%22en-US%22%3ERe%3A%20SUM.IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390824%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20prompt%20reply.%3C%2FP%3E%3CP%3EUnfortunately%20leading%20numbers%20are%20not%20always%20the%20same.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENevertheless%20in%20the%20REMARKS%20in%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fsumif-function-169b8c99-c05c-4483-a712-1697a653039b%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fsumif-function-169b8c99-c05c-4483-a712-1697a653039b%3C%2FA%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3Eis%20said%20that%20%22%3CSPAN%3EThe%20SUMIF%20function%20returns%20incorrect%20results%20when%20you%20use%20it%20to%20match%20strings%20longer%20than%20255%20characters%20or%20to%20the%20string%20%23VALUE!.%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ESo%20I%20thought%20that%20the%20problem%20was%20not%20lenght%20of%20the%20criteira%20string.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks%20a%20lot%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Erinaldo%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390815%22%20slang%3D%22en-US%22%3ERe%3A%20SUM.IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390815%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F310545%22%20target%3D%22_blank%22%3E%40rc14354%3C%2FA%3E%26nbsp%3B%2C%20yes%2C%20Excel%20recognizes%20only%20first%2015%20digits%20and%20drops%20the%20rest%20when%20you%20do%20calculations%20with%20such%20values%20as%20with%20numbers.%20As%20workaround%2C%20if%20you%20leading%20digits%20are%20always%20the%20same%2C%20you%20may%20split%20that%20column%20on%20two%2C%20first%2010-15%20digits%20and%20the%20rest%20(Data-%26gt%3BText%20to%20Columns)%20and%20operate%20with%20the%20second%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E
rc14354
New Contributor

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

9 Replies

@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.

@Sergei Baklan 

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

@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?

@Sergei Baklan 

I do not convert first column in text but I use them directly as criteria

Solution

@rc14354 , I see. The workaround is to use SUMRODUCT

image.png

Please check in attached file

@rc14354 

Time to bring up this little piece:

The Countif bug (and how to avoid it)

Oh, yes, I totally forgot about magic of the CHAR(173). Thank you, @Detlef Lewin 

@Detlef Lewin Hey just noticed this link! Indeed for numeric text you can use:

=SUMIF(range, CHAR(173)&criteria, [sum_range])

Glad others have picked up on this and it's been put to good use :)

@lori_m 

Ah! The discoverer herself.

Solving an eleven year old problem which annoyed almost every Excel user is always worth mentioning.

 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
ChirmyRam in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies