Excel 2013 sum function returns 0 for range of nonzero numbers

Question

I'm having problems with what I would hope to be the most robust and simple excel formula. I was given a "#DIV/0" error when trying to calculate the proportion of a cell's content to the sum of the cells in a range (e.g. "=AO35/SUM(AO35:A039)). Cells in the referenced range were formatted as numbers, and I've also tried "general."  Digging into the next layer, excel returns a value of 0 for both "=SUM(AO35:AO39)" and "=SUM(AO35,AO36,AO37,AO38,AO39)" which have values of 3, 41, 1, 3, 15. Now if I enter the formula "=AO35+AO36+AO37+AO38+AO39" excel returns the correct value of 63. Am I missing something here? because I have long been under the impression that the sum of a set of numbers is obtained by adding them together. If this is false then my whole mathematical world of calculating SD, SEM, residuals, etc. is about to come crashing down.

The one and only factor that I can think of which could be effecting this (assuming that the coding for excel could possibly be flawed) is that I had copied the worksheet in question into an existing workbook while retaining the original theme (to retain cell colors). Both were created in the same version of excel though. Again, I have checked the cell formatting for the range of cells, as well as for the formula cell.

Any help, advice, or insight is greatly appreciated.

~D

Answer 1

I found that the sum returning 0 contained one element that was a circular reference - not created by me I hasten to add! :-)  Once I corrected the circular reference, the =SUM() function returned the expected result.