The SUM function in Numbers 3.6.1 on my Mac OS X10.10.5 isn't calculating correctly. Why?

Question

You can see in the attached screenshot that I have used the SUM function to add the values in column E. There are several "1" values (you can see one in cell E126) but the SUM always comes out at "0". Why is this? To create this spreadsheet, I duplicated a Numbers spreadsheet in which I used "x" to denote a positive value (presence of different types of bees), then I used Find and Replace to change each "x" to "1" in this spreadsheet so I could use Numbers to compute a sum for each column. Would this find-and-replace maneuver make the values unable to be counted by the SUM function? How can I get Numbers to return a correct SUM for each column?

Answer 1

If the cells are in text format, they won't be counted. Check they are a number format cell first.

EDIT I have tried it myself, when you find and replace, the cell is not automatically updated to a number cell. If you manually type in the number 1, it automatically gets changed to a number format.

Answer 2

Another way of doing this without having to convert the text to number:

Use the COUNTIF() function.

This way, you don’t even have to convert your "x" to a 1 either:

=COUNTIF(E3:E130, "x")

This counts all the "x"'s in the column. Same as summing up a bunch of 1's.

The SUM() function is not preferred for your situation. Suppose one of the numbers becomes 2 or 11 by mistake. As you really are looking for a COUNT of how many are '1' the count functions COUNT, COUNTIF, COUNTIFS are much better tools for this particular job.