Forum Discussion
SUM function not working after nested IF creation
I have a simple Excel spreadsheet where I record my animal weights and then classify each animal by a value (Animal Units) in the last column. I created two nested IF formulas (one for males, one for females; both returned expected results) to decide each animal's AU in the last column. However, the SUM formula at the bottom of the column now returns "0" instead of the total of the numbers in the column. The correct SUM should be "51.40." The cells where the results from the nested IF formulas are returned are formatted as Numbers with two decimal points.
Can anyone suggest what has happened? When I enter the AU numbers manually in a different column the SUM formula works fine, so I'm thinking it must have something to do with the nested IF cells, but why?
Cell formatting merely determines how Excel will 'show' the contents of the cell. Ultimately, it comes down to: does Excel view it as text or a number?
For example, here SUM returns 0 because the numbers are treated as text (Enclosed in double quotes):
If your numbers are being treated as text, you can use one of methods described here to convert them to numbers so SUM will return an actual total.
Convert numbers stored as text to numbers - Microsoft Support
