Adding Values from Formulas

[Guest]

I'm sure there is a very easy way to do this, but right about now my brain is
fried.\
I have a column of values that are the results of formulas. These values
are scores based on the criteria set in each formula. I now need to add the
scores and come up with a total score. I keep returning a "0". Any ideas?
Thanks!

 

[Dave O]

Just how fried is your brain? Is it possible that the sum of those
values is zero? Or, it may be your formula: i sometimes intend to
enter =SUM(B1:B5) and enter just the (B1:B5) part, which does not
necessarily yield an error.

 

[Guest]

Are you using the SUM() function that is returning a 0?

If so, your data might be formatted as text. Try copying an empty cell,
selecting the range of values you're trying to sum and "Paste Special"->Add
to turn them into numbers.

 

[Guest]

if your scores are in A1 thru A10, then in an un-used cell enter:
=SUM(A1:A10)

 

[Mark Lincoln]

Are the cells you're trying to sum formatted as Text?

 

[Guest]

That works, however it will not recalculate when the original values are
changed. The cells will be blank and then different people will fill in their
numbers to get their scores. I need it to be able to recalculate each time
the value is changed. Is there a way to do this?

 

[Dave O]

Tools >Options; click the Calculation tab; set to Automatic.

 

[Guest]

Not that fried. The sum is 23. The sum function is correct, and the
cells are formatted as numbers. If I copy the values using paste special to
an unused cell, I can sum those totals, but then the total does not
recalculate if the values are changed in the original cells.

 

[Dave O]

Where you had pasted as values, try the formula =VALUE(cell reference).

 

[Mark Lincoln]

Can you give us an example of a formula from a cell in the range you're
trying to total? It shouldn't be too difficult to sum a series of
numbers, after all.

 

[Guest]

Hmm, it might be still formatting the cells as text. Try to change your
SUM() function to the following:

=SUM(VALUE(...))

And change the "..." to your range. Enter this with CTRL+SHIFT+ENTER, as
it's an array formula. Doing it this way will always convert to numbers
before calculating the sum.