why is this not adding and instead giving me a 0???

[San Antonio Heat]

=SUM(F2,J2,N2,T2,V2,X2,Y2) I want to add these and get the total in cell AB2 but everytime I try it I get a 0, can someone tell me why?

nba.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Cost	Guards	Team			FG's				3's				FT's						Rbd		***		Stl	Blck		Points
2	10	AllenIverson	PHI	21	43.7	9.9	24.9	39.7		1.4	4.6	30.9		7.6	10.1	75.8		0.8	3.0	3.8		6.1	4.3	2.9	0.1	2.0	28.9	0
3	8	RayAllen	SEA	5	33.0	8.8	19.4	45.4		3.0	8.2	36.6		4.2	5.0	84.0		1.2	3.4	4.6		5.2	2.8	1.2	0.2	1.4	24.8
4	8	TracyMcGrady	ORL	32	39.3	8.6	21.1	41.0		2.1	6.3	32.8		5.0	6.1	81.1		1.3	5.2	6.5		5.9	3.0	1.2	0.8	2.0	24.3
5	8	BaronDavis	NO	33	40.9	8.6	22.1	38.9		3.0	9.4	31.7		3.4	5.3	63.1		1.0	3.2	4.2		8.4	3.0	2.6	0.5	2.7	23.6
6	8	PaulPierce	BOS	34	38.5	7.8	18.9	41.5		1.6	4.6	33.8		6.0	7.3	83.0		1.0	6.3	7.3		5.8	4.3	1.6	0.8	2.7	23.3
salaries

 

[dk]

The cells are probably formatted as text. You can confirm this by clicking in an empty cell and typing =ISTEXT(F2). If it says TRUE then the cells are text and you'll need to convert them to numerical values before any SUM formulas will work. One way of doing this is this:-

1. Type 1 in a blank cell.
2. Select all the range you want to convert.
3. Press F5 and click Special. Selecyt Constants in the Go To Special dialog box.
4. Click Edit, Paste Special and choose Values in the Paste section and Multiply in the Operation section.
6. Click OK. Voila!

 

[dk]

The cells are probably formatted as text. You can confirm this by clicking in an empty cell and typing =ISTEXT(F2). If it says TRUE then the cells are text and you'll need to convert them to numerical values before any SUM formulas will work. One way of doing this is this:-

1. Type 1 in a blank cell.
2. Select all the range you want to convert.
3. Press F5 and click Special. Selecyt Constants in the Go To Special dialog box.
4. Click Edit, Paste Special and choose Values in the Paste section and Multiply in the Operation section.
5. Click OK. Voila!

EDIT : Oops, I meant to edit my initial post but I must have done something stupid

 

[parry]

Hi, the only thing I can think of is that Excel is seeing the values as text so isnt adding them. When you look at the value in the cell, is the value left aligned or right aligned? Cells that have their values left aligned are text, while values right aligned are numbers. It needs to be numbers.

If this is the problem you may have to delete the values and manually enter them. A quick way is by placing a 1 in a blank cell, click copy to copy its value then select the cells in the row and select paste-special then choose the Multiply option. It doesnt always work though so you may have to manually enter the values.

EDIT: Oops I see Dan has beaten me to it. err twice even

 

[Brian from Maui]

How are the values being entered?

 

[San Antonio Heat]

copy from a site and pasted and yes they show a left allign, let me ttry your way. If I go back and maually enter each number then it works but that would take forever

 

[Brian from Maui]

copy from a site and pasted and yes they show a left allign, let me ttry your way. If I go back and maually enter each number then it works but that would take forever

What Dan and Parry offered as solutions shouldn't take very long.

 

[parry]

Hi, as the multiply method doesnt always work heres some code to do it. Just select the cells that are text then run the macro. This will be a bit quicker than doing it manually if you have a lot of cells to fix.

Sub ConvertText()
Dim c

For Each c In Selection
c.Value = Val(c.Value)
Next c
End Sub

 

[Aladin Akyurek]

copy from a site and pasted and yes they show a left allign, let me ttry your way. If I go back and maually enter each number then it works but that would take forever

You have probably a CHAR(160) around the numbers., which you can remove with TrimAll macro, whose code is posted in a few threads at this site.