sums are wrong

[jill]

I have never had this happen - I am doing a basic
spreadsheet and want to add up a number of cells and am
using the auto sum. It is not adding correctly! Also, my
cell formats aren't right. When I set them to currency
they aren't showing dollar signs. What's up?

 

[Skills]

Make sure the dollar sign is selected in format cells

 

[Trevor Shuttleworth]

Jill

sounds like some of your "numbers" are actually text pretending to be
numbers. This would explain the sum being wrong and the failure of the
cell(s) to accept the currency format. Any "numbers" aligned to the left
are not really numbers. Where did the data come from ?

Regards

Trevor

 

[Norman Jones]

Hi Jill,

This behaviour is consistent with the data cells having been previously
formatted as text.

To rectify:

Select a blank cell
Edit | Copy
Select the problem cell range
Edit | PasteSpecial | Check Values | Check Add | Ok

Your cells will now display as currency and your sum totals will return the
expected values.

 

[JILL]

OK guys.
I've tried all your suggestions. Nothing is working.
I am trying to keep track of numbers of shirts sold for the
elementary school. Different sizes. Different costs and
margins. I just want to make it all add up! I am entering
numbers of shirts sold (by color, size, type) and just want
to add the things up. This is basic. I've nver had aan
issue with this!
Thanks for the help.
JILL

 

[Aladin Akyurek]

Assume that the range you want AutoSum is B2:B100.

[1]

=SUM(B2:B100)

should give you what you want.

Whether that is the case can be checked/audited with:

[2]

=SUMPRODUCT(--B2:B100)

If the results of [1] and [2] are not equal, you can conclude that there are
text-formatted numbers in B2:B100. If the existence of the text-formatted is
unjustified, you can convert such numbers to true numbers. Norman Jones in
this thread has given you the procedure with which to effect the required
conversion.