Home

When I add up formula results in my excel files it works A file sent to me I get 0 when I sum??

%3CLINGO-SUB%20id%3D%22lingo-sub-202261%22%20slang%3D%22en-US%22%3EWhen%20I%20add%20up%20formula%20results%20in%20my%20excel%20files%20it%20works%20A%20file%20sent%20to%20me%20I%20get%200%20when%20I%20sum%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-202261%22%20slang%3D%22en-US%22%3E%3CP%3EWhen%20I%20add%20up%20formula%20results%20using%20auto%20sum%20on%20my%20computer%20no%20problem%20to%20get%20total.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20co%20worker%20sent%20me%20a%20file%2C%20I%20try%20to%20add%20up%20formula%20results%20and%20I%20get%200.%26nbsp%3B%20If%20I%20change%20values%20to%20static%20it%20adds%20up%20fine.%26nbsp%3B%20If%20I%20create%20a%20sum%20formula%20in%20another%20column%20and%20then%20drag%20over%20it%20works%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20a%20setting%20issue%20as%20to%20why%20it%20wont%20add%20up%20these%20formula%20results.%26nbsp%3B%20Never%20seen%20anything%20like%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-202261%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-202283%22%20slang%3D%22en-US%22%3ERe%3A%20When%20I%20add%20up%20formula%20results%20in%20my%20excel%20files%20it%20works%20A%20file%20sent%20to%20me%20I%20get%200%20when%20I%20sum%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-202283%22%20slang%3D%22en-US%22%3EHaytham%20-%20thanks%20for%20the%20response%2C%20I%20will%20use%20that%20!!!!!!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-202281%22%20slang%3D%22en-US%22%3ERe%3A%20When%20I%20add%20up%20formula%20results%20in%20my%20excel%20files%20it%20works%20A%20file%20sent%20to%20me%20I%20get%200%20when%20I%20sum%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-202281%22%20slang%3D%22en-US%22%3EOkay%20I%20figured%20it%20out.%20The%20cells%20I%20was%20trying%20to%20add%20formula%20results%20which%20contained%20a%20circular%20reference%20so%20it%20gave%20me%20a%200%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-202264%22%20slang%3D%22en-US%22%3ERe%3A%20When%20I%20add%20up%20formula%20results%20in%20my%20excel%20files%20it%20works%20A%20file%20sent%20to%20me%20I%20get%200%20when%20I%20sum%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-202264%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Steve%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20seems%20that%20the%20numbers%20you%20got%20from%20your%20co-worker%20are%20treated%20as%20texts%20not%20as%20numerical%20numbers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20one%20of%20the%20fastest%20ways%20to%20parse%20them%20and%20convert%20them%20to%20the%20numeric%20status%20is%3A%3C%2FP%3E%3CUL%3E%3CLI%3EHighlight%20all%20numbers%3C%2FLI%3E%3CLI%3EGo%20to%20Data%20(Ribbon%20Tab)%20%26gt%3B%26gt%3B%20Data%20Tools%20%26gt%3B%26gt%3B%20Text%20to%20Columns%3C%2FLI%3E%3CLI%3EFrom%20the%20Text%20to%20Columns%20wizard%2C%20hit%20%3CSTRONG%3EFinish%3C%2FSTRONG%3E%20button%3C%2FLI%3E%3C%2FUL%3E%3CP%3EAfter%20that%2C%20try%20to%20auto%20sum%20these%20numbers%2C%20and%20tell%20me%20what%20you%20think!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-553935%22%20slang%3D%22en-US%22%3ERe%3A%20When%20I%20add%20up%20formula%20results%20in%20my%20excel%20files%20it%20works%20A%20file%20sent%20to%20me%20I%20get%200%20when%20I%20sum%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-553935%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20same%20problem%20since%20I%20upgraded%20office360.%3C%2FP%3E%3CP%3Ehow%20do%20I%20take%20a%20circular%20reference%20out%3F%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F154388%22%20target%3D%22_blank%22%3E%40Steve%20Carey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Steve Carey
New Contributor

When I add up formula results using auto sum on my computer no problem to get total.

 

A co worker sent me a file, I try to add up formula results and I get 0.  If I change values to static it adds up fine.  If I create a sum formula in another column and then drag over it works fine.

 

Is this a setting issue as to why it wont add up these formula results.  Never seen anything like this.

4 Replies

Hi Steve,

 

It seems that the numbers you got from your co-worker are treated as texts not as numerical numbers.

 

However, one of the fastest ways to parse them and convert them to the numeric status is:

  • Highlight all numbers
  • Go to Data (Ribbon Tab) >> Data Tools >> Text to Columns
  • From the Text to Columns wizard, hit Finish button

After that, try to auto sum these numbers, and tell me what you think!

Okay I figured it out. The cells I was trying to add formula results which contained a circular reference so it gave me a 0
Haytham - thanks for the response, I will use that !!!!!!!

I have same problem since I upgraded office360.

how do I take a circular reference out? @Steve Carey 

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies