2

enter image description here

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?

2

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.

  • Can I change them to a number format all at once? Or do I have to re-type all the numbers in the cells? – Ann of NC Jan 26 '16 at 0:27
  • 2
    yes, select rows E3 to E130 with the mouse, then in the format panel on the right, choose the "Cell" tab and "data format" is the first option - change it to number, or automatic. – user3323052 Jan 26 '16 at 0:33
0

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.

COUNTIF formula example

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .