Formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-1602734%22%20slang%3D%22en-US%22%3EFormulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1602734%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20just%20changed%20from%20excel%202016%20to%202019%20and%20having%20trouble%20with%20summing%20totals.%26nbsp%3B%20I%20can%20total%20row%20but%20the%20sum%20to%20total%20coumn%20is%20incorrect.%26nbsp%3B%20Does%20the%20formula%20link%20not%20work%20is%20there%20is%20blank%20rows.%26nbsp%3B%20I%20am%20completely%20confused%20as%20I%20have%20worked%20with%20complicated%20formulas%20for%20most%20of%20working%20life.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1602734%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1603016%22%20slang%3D%22en-US%22%3ERe%3A%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1603016%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F766348%22%20target%3D%22_blank%22%3E%40Patricia_Taylor%3C%2FA%3E%26nbsp%3BYou%20are%20summing%20hours%20and%20minutes.%20Excel%20interprets%20it%20as%20time%20value.s%20As%20soon%20as%20your%20total%20exceeds%2024%20hour%2C%20it%20be%20displayed%20as%20a%20time%20value%20for%20the%20next%20day.%20So%2C%20the%2029%20hrs%20and%2015%20minutes%20are%20represented%20as%2005%3A15%20(past%20midnight).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20a%20custom%20format%20%5Bh%5D%3Amm%20to%20display%20the%20sum%20as%20you%20want%20it%20to.%20See%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi, just changed from excel 2016 to 2019 and having trouble with summing totals.  I can total row but the sum to total coumn is incorrect.  Does the formula link not work is there is blank rows.  I am completely confused as I have worked with complicated formulas for most of working life.

 

Please help.

1 Reply
Highlighted

@Patricia_Taylor You are summing hours and minutes. Excel interprets it as time value.s As soon as your total exceeds 24 hour, it be displayed as a time value for the next day. So, the 29 hrs and 15 minutes are represented as 05:15 (past midnight). 

 

Use a custom format [h]:mm to display the sum as you want it to. See attached.