SOLVED
Home

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
Highlighted
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
Highlighted

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

Highlighted

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

Highlighted

@Sergei Baklan 

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

Highlighted
Solution

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

image.png

Please check in attached file

Highlighted

@rc14354 

Time to bring up this little piece:

The Countif bug (and how to avoid it)

Highlighted

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

Highlighted
@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 :)
Highlighted

@lori_m 

Ah! The discoverer herself.

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

 

Related Conversations
Excel If Functions
Mfouad2255 in Excel on
10 Replies
Help with an IF AND formula
aanaya6 in Excel on
3 Replies
Query Help please
Tim Hunter in SQL Server on
1 Replies
IF FUNCTION ISN'T WORKING NO MATTER HOW SIMPLE THE COMMAND IS
thomasea in Excel on
6 Replies
2 lists, 1 if
Bartosz Heller in Excel on
4 Replies