Why can't I sum a column in Numbers

Hi Tom,

There appears to be at least one error in those instructions, when applied to Numbers 3.6.2. It's the one you noticed, stating that "Numbers chooses a result cell based on your selection." Tha doesn't seem to happen.

Here's a sample showing the process I think those instructions are supposed to be describing.

(The numbers in this table are all randomly generated, and are recalculated any time there is a change made in the table. The change in the displayed sum from one image to another is due to this change in the numbers in columnD, the column being summed.)

In the first image, I have selected the cells to be summed. A box appears at the bottom of the document window, showing five calculations I might want to do with the selected values, and the current result of each of the calculations.

I want to SUM the numbers in this column, so I will drag the SUM lozenge indicated by the lower end arrowhead up, and drop it into the empty cell at the bottom of column D.

After dragging and dropping the SUM lozenge into cell D11, the box shows the formula that has ben created in the selected cell, D11, and the cell displays the results of the SUM calculation.

When the cell is deselected, the box will either disappear or show the data or formula in a newly selected cell or the same set of calculations for the newly selected range of cells. The red curve with arrows at each end was added manually, and must be selected and deleted manually to make it disappear.

Regards,

Barry

you should NOT type the dollar sign in the cell. use formatting from the cell formatter.

just type positive or negative numeric values, then format by selecting the cells, then click the button "Format" at the top right, then select the segment "Cell", then choose the "Currency" from the pop-up "Data Format":

Hi Tom,

The "quick look" calculations at the bottom of the screen are good for a quick look.

Try this for a more dynamic solution:

A table with a Header Row and a Footer Row. The formula in the Footer Cell A7 =SUM(A) sums all the Body Cells (A2 to A6) in Column A and ignores the Header Row (Row 1) and the Footer Row (Row 7). That avoids a "self reference" to A7.

As you add or delete Body Rows to the table, the formula automatically adjusts to include all Body Cells in column A:

Regards,

Ian.

@Wayne: Good eye!! I'd either not noticed the $ signs or assumed they were there by formatting as currency.

That would also explain why Tom was't seeing the quick calculation buttons—entered directly, "$123" is text, and can't be summed (or averaged).

Hi Tom,

If I'm not mistaken, you should have a "Solved" left, which would fit nicely on Wayne's post.

Regarding what you said above: "…discovered that those entries couldn't even be copy/pasted anywhere - apparently

b/c they're products of other cells i.e. the values were sometimes entered as just =

to the value of another cell. I guess standing on their own they have -0- value and

can't be used in a calculation - they only have value in the place the formula was

entered."

"=B2" is formula. If you copy a cell containing a formula, you are copying the formula (and its current result).

If you then paste that formula into a different cell, Numbers adjusts the formula to point at a cell in the same relative position with respect to the pasted location as it pointed to with respect to the original location.

Example:

B2 contains 7, D5 contains the formula =B2 (two columns to its left and three rows above it), and displays 7.

You click on D5, and Copy, then click on H18 and Paste. The formula adjusts to read =F15 (two column left of and three rows above H18). F15 happens to be an empty cell, which the formula interprets as possibly a numerical value, and displays 0.

Second example:

B2 still contains 7, D5 still contains =B2 and displays 7.

You click on D5 and Copy.

You click on H18, then go to the Edit menu and choose Paste Formula Results.

H18 now contains the number 7, the last result calculated by the formula in D5, the cell that was copied.

If you now change the value in B2 to 42, D5's value with change to match B2; H18 will continue to contain 7.

Third example:

B2 contains 7, D5 contains =B2 and displays 7.

You double click on D5 opening the Formula editor, the press command-A to select everything in the editor, then command-C to Copy, then click the green check mark on the editor to close it.

You click on H18, then type = to open the formula editor, then paste,

Because the formula was copied as text, it goes into the editor without any changes, and still reads =B2. When the editor is closed (using the green checkmark) it displays 7.

If you now change the value inn B2 to 42, D5 also changes to 42 and H18 also changes to 42.

Happy Numbers-ing,

Regards,

Barry

You can quickly find the sum, average, minimum, maximum, count, or product of a range of cells.

1. Do one of the following:
   • Select the range of cells you want to include in your formula. Numbers chooses a result cell based on your selection.
   • Select a cell to display the result of the formula. You can’t insert a formula in a header cell.
2. Click +v in the toolbar, then choose a formula.
3. If you want to change the range of cells, double-click the result cell. The cells used in the formula are highlighted, and the formula editor appears. Do any of the following:
   • Resize the selection of cells: Drag the colored dot in the top-left or bottom-right corner of the range of selected cells.
   • Move the selection: Click the range of selected cells, then drag up or down to change which rows are used, or drag right or left to change which columns are used. Moving the selection doesn’t change the number of cells selected.CAUTION: Make sure the selection doesn’t include the result cell—this causes an error in the cell.
4. Click tick(checkmark) to save your changes.

Read here on how to sum:

https://help.apple.com/numbers/mac/4.3/#/tan727173a8