You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
Hi
I have to sum cells within a column with the function SUM, say SUM (c4:c9).
Some cells have values, some have a formula taking the values from other cells not in the same row.
The SUM only takes into account the cells with values and NOT the cells with the formula even if they are showing the result of the formula as a value.
So, I cannot sum all the cells.
Is the re a way to solve this problem?
Thank you for your help.
Gene
OK thanks the difference between numbers and values explains it all. If there is a value cell in the range I've better split the sum or add every cell with the +.
Thanks!
There is always a "value cell" in the range.
Whatever content is displayed in a cell is a "value", "number" and "text" are different types of value, as are "durations" , and "date and time values".
Assuming the values you want to sum are in a single column (column B, for the example),, you might try this:
Add a new column to the table. In the example, I added column F.
In the example, the values in columns C, D and E are numbers.
Column B contains this formula, entered in B2, and filled down to B5: C2&D2&E2
The formula joins the characters displayed in the three named cells into a single text string, as displayed in the column.
Row 10 has been converted to a Footer row to permit using a 'whole column' reference rather than a defined range in the SUM formula in each cell of that row. Each cell, except A10, contains the formula SUM(B) with B replaced by the letter of the column containing the formula.
As can be seen, the SUM in column B, where all the values are text, is zero.
Sums in columns C, D and E arw correct (as is the sum in column F.
Column F contains this formula, entered in F2, and filled only to F5:
VALUE(B2)
VALUE converts text expressing numbers to the numeric value of the expressed number. Where the 'number' is actually a number, VALUE makes no change.
More on VALUE in the Function Browser.
Regards,
Barry
Hello dijjiman,
We understand that you have some questions about calculating a sum in Numbers, and we'd like to get you pointed in the right direction.
Since this issue is a bit more complex than the information provided in the Help section of Numbers, we’d like you to contact Apple Support.
Get Support --Â Choose Music, Apps & Services > Mac apps > Numbers to get the conversation started.
Take care!
The usual problem is that the "values" are text, not actual numbers. SUM will skip over any values that are text, even if they are numeric. You did not post a sample formula (which may also require us seeing all cells referenced in the formula) so I cannot verify it is the problem but it is the most likely.
As a side note, the + operator will add numeric text but it requires a + between every cell reference vs using a range reference. It would probably be better to fix the formulas, or the cells that are referenced in the formulas, so the result of the formulas is an actual number format, not text.
If the "numbers" are supposed to be actual numbers, it might be better to edit the formulas to make them that way so you can use them in your SUM (or any other formulas that you might want to use them in). Very few (if any) numeric formulas will accept textual "numbers". A simple solution to convert from text to number is to use the VALUE function or to add 0 to the formula. If that does not work, if you provide a screenshot that shows the formula and the cells it references, we can give you some other ideas.
="123" will create a text value of 123 that cannot be used in numeric formulas
=VALUE("123") will create the number 123 which can be used in numeric formulas
="123"+0 will also create the number 123 which can be used in numeric formulas.
How to sum cells containing a formula
