Home

SUM Function Not Working

%3CLINGO-SUB%20id%3D%22lingo-sub-179886%22%20slang%3D%22en-US%22%3ESUM%20Function%20Not%20Working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-179886%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20that%20I%20created%20a%20nested%20if%20statement%20to%20fill%20in%20a%20number%20value%20in%26nbsp%3B%20cell%20J2%20%22%3DIF(G2%3D%22L%22%2C%220%22%2CIF(G2%3D%22OTL%22%2C%221%22%2CIF(G2%3D%22W%22%2C%222%22%2CIF(G2%3D%22%22%2C%22%22))))%22%26nbsp%3B%20it%20works%20fine%20and%20puts%20the%20proper%20number%20in%20J2.%26nbsp%3B%20It%20does%20this%20for%20cells%20G2%3AG83.%26nbsp%3B%20The%20number%20value%20goes%20into%20cells%20J2%3AJ83.%26nbsp%3B%20I%20then%20used%20the%20SUM%20function%20to%20total%20the%20numbers%20in%20cells%20J2%3AJ83%20however%2C%20the%20sum%20shows%20as%200%20instead%20of%20the%20actual%20sum.%26nbsp%3B%20What%20if%20anything%20am%20I%20missing%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-179886%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766767%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%20Function%20Not%20Working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766767%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%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20there%20-%20I%20have%20a%20similar%20problem%20as%20your%20previous%20poster%2C%20but%20have%20checked%20my%20cells%20and%20no%20text%20fields%20there%2C%20but%20I%20still%20get%20%240%20for%20the%20sub-total.%26nbsp%3B%20I've%20done%20this%20many%20times%20and%20no%20problem%2C%20but%20just%20can't%20seem%20to%20pinpoint%20where%20the%20issue%20is.%26nbsp%3B%20I've%20put%20a%20red%20box%20around%20the%20culprit%20-%20hope%20you%20can%20help.%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3ETeresa%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-309271%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%20Function%20Not%20Working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309271%22%20slang%3D%22en-US%22%3E%3CP%3ESmall%20triangle%20means%20you%20have%20a%20text%2C%20not%20numbers.%20If%20you%20select%20your%20cells%20and%20click%20on%20arrow%20within%20the%20appeared%20icon%20you'll%20see%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20222px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F64731i2021B8851142F0B1%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%3EYou%20may%20convert%20to%20numbers%20from%20that%20menu%2C%20cells%20format%20will%20be%20shifted%20to%20General.%20Before%20that%20you%20may%20check%20how%20you%20cells%20are%20formatted%2C%20select%20them%20and%20click%20Ctrl%2B1.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-309232%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%20Function%20Not%20Working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309232%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20entered%20%3DSUM(E3%3AE9)%20but%20all%20it%20shows%20is%200%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20correct%20cells%20are%20addressed%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22%22%3ENo%20%E2%80%98%20mark%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3ENo%20space%20ahead%20of%20number%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3EAll%20cells%20are%20formatted%20as%20number%20(both%20source%20and%20destination)%20using%20FORMAT%20function.%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3EThere%20is%20a%20small%20triangle%20in%20upper%20LH%20side%20of%20source%20cells%20but%20I%20cannot%20figure%20what%20it%20means%20or%20how%20to%20find%20out.%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-179936%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%20Function%20Not%20Working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-179936%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%2C%20that%20did%20it.%26nbsp%3B%20I%20knew%20I%20was%20missing%20some%20stupid%20little%20obvious%20thing.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-179934%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%20Function%20Not%20Working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-179934%22%20slang%3D%22en-US%22%3E%3CP%3EMark%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%221%22%3C%2FSTRONG%3Eis%20text%20and%20not%20a%20number.%20SUM()%20adds%20only%20numbers%20and%20ignores%20text.%20Change%20%3CSTRONG%3E%221%22%3C%2FSTRONG%3Eto%20%3CSTRONG%3E1%3C%2FSTRONG%3E%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766828%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%20Function%20Not%20Working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766828%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379876%22%20target%3D%22_blank%22%3E%40TeresaHan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20not%20clear%20which%20cell%20you%20are%20referring%20to%20but%20your%20spreadsheet%20has%20a%20circular%20reference%20in%20AL4%3AAL8.%20That%20may%20be%20the%20cause.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Mark Akesson
New Contributor

I have a spreadsheet that I created a nested if statement to fill in a number value in  cell J2 "=IF(G2="L","0",IF(G2="OTL","1",IF(G2="W","2",IF(G2="",""))))"  it works fine and puts the proper number in J2.  It does this for cells G2:G83.  The number value goes into cells J2:J83.  I then used the SUM function to total the numbers in cells J2:J83 however, the sum shows as 0 instead of the actual sum.  What if anything am I missing? 

6 Replies

Mark

 

"1" is text and not a number. SUM() adds only numbers and ignores text. Change "1" to 1,

Thanks, that did it.  I knew I was missing some stupid little obvious thing.

I entered =SUM(E3:E9) but all it shows is 0

 

The correct cells are addressed

No ‘ mark
No space ahead of number
All cells are formatted as number (both source and destination) using FORMAT function.
There is a small triangle in upper LH side of source cells but I cannot figure what it means or how to find out.

Small triangle means you have a text, not numbers. If you select your cells and click on arrow within the appeared icon you'll see like

image.png

You may convert to numbers from that menu, cells format will be shifted to General. Before that you may check how you cells are formatted, select them and click Ctrl+1.

@Sergei Baklan 

 

Hi there - I have a similar problem as your previous poster, but have checked my cells and no text fields there, but I still get $0 for the sub-total.  I've done this many times and no problem, but just can't seem to pinpoint where the issue is.  I've put a red box around the culprit - hope you can help.

Regards,

Teresa

@TeresaHan 

It is not clear which cell you are referring to but your spreadsheet has a circular reference in AL4:AL8. That may be the cause.

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies