I have a column of cells that contains A1, A3, A5, A7, A9, A11, and A13, which I need to add. When I tried =SUM(A1,A3,A5,A7,A9,A11,A13), I got "0" as the answer; however, when I used =A1+A3+A5+A7+A9+A11+A13 with all of these cells containing a number, I get the correct answer. When I deleted one number, using this method, I got an error message (#VALUE!), so I can't use that approach because all seven cells will not always contain numbers. Thank you.

It's not the formulae, it's probably the formatting of the cells that contain your data. Select any empty cell and Copy it. Select the cells you want to Sum and do an Edit...Paste Special...Add.

Let us know if that solves your problem.

BTW: If you use =A1+A3+A5+A7+A9+A11+A13 you will still get a #VALUE error if any of the cells contain text.

P.S. That should give you a hint as to why adding 0 to the cells solved the problem - assuming it does.

I first checked the formatting of the cells that I wanted to add; they were all formatted the same way as "Number". I tried Copy>Paste Special>Add but that didn't work; I got a blank cell.

What do you mean "I got a blank cell"?Are you saying that you had data in a cell, you selected an empty cell and when you did a PasteSpecial...Add the data in the original cell disappeared?

I "copied" a cell, and then highlighted the cells that I wanted to add. I then did Edit>Paste Special>Add. The cell that I had copied remained blank and a "0" was added to the numbers that I wanted to add, i.e., "36" became "036".

How do I say this correctly... I'm not doubting that you saw what you saw. because you say you saw it, but I don't believe that you saw what you saw.

Does that make sense?

Let me explain:

re:

The cell that I had copied remained blankYes, that is correct. There's no reason that the empty cell that was copied would change in any way.

re:

"36" became "036"This is hard to believe. Edit PasteSpecial...Add does an arithmetic Add of the value on the clipboard to the value in the cell.

Since Excel likes to work with numbers, even if a number in a cell is formatted as text, Excel will try to Add the value to the cell, which will convert the text-number to a number.

Adding a zero (which is sort of what ends up on the clipboard when you copy a blank cell) should simply convert the text to a number.

Therefore a "text" 36 should become a "number" 36 and thus be able to be added via the SUM function.

Now, I guess that there is a possibility that there is some Custom Formatting on the cell that adds the leading zero to any number in the cell, but you said that the cells were formatted as Numbers, not as Custom. Since don't know of any Number format that adds leading zeros, I don't see how a number, formatted as a Number, could display 036.

I've PM'd an email address, which I believe you already have. Send me a copy of the sheet with the problem and I'll see what I can do.

Brian, I also am unable to duplicate your problem, except if there is TEXT somewhere in your figures and I did not use the correct function to do the addition. IE I input 6 numbers and the word "dog" in the A column and:

=SUM( A13, A11, A9,A7,A5,A3,A1) Gives Correct total

=SUM(A1:A13) Gives Correct total=SUM( A13+ A11+ A9+A7+A5+A3+A1) Gives #VALUE error

= A13+ A11+ A9+A7+A5+A3+A1 Gives #VALUE error.How are these cells being populated? Direct input?

MIKE

I think I may have discovered what’s going on: In my previous post I populated column A with 6 Numbers and the word “dog”

Cell__A1 Dog

Cell__A3 10

Cell__A5 10

Cell__A7 10

Cell__A9 10

Cell_A11 10

Cell_A13 10=SUM( A13, A11, A9,A7,A5,A3,A1) Gives Correct total of 60

=SUM(A1:A13) Gives Correct total of 60=SUM( A13+ A11+ A9+A7+A5+A3+A1) Gives #VALUE error

= A13+ A11+ A9+A7+A5+A3+A1 Gives #VALUE error.But, I just went back to the same sheet and simply replaced the Word “dog” with the number 1 and the addition functions that returned #VALUE error message now gave me the correct total while the functions that were correct before are now off by 1,

Cell__A1 1

Cell__A3 10

Cell__A5 10

Cell__A7 10

Cell__A9 10

Cell_A11 10

Cell_A13 10=SUM( A13, A11, A9,A7,A5,A3,A1) Gives what appears to be INcorrect total of 60

=SUM(A1:A13) Gives what appears to be INcorrect total of 60=SUM( A13+ A11+ A9+A7+A5+A3+A1) Gives what appears to be Correct total of 61.

= A13+ A11+ A9+A7+A5+A3+A1 Gives what appears to be Correct total of 61.I then checked the Format for A1 and it was still set to TEXT, I changed it to Number, but it had NO effect on the totals…….I even did a force recalc.

Not until I RE-entered the number one, after I had changed the format to NUMBERS did it change the totals.

So it would appear that the FORMAT of the cell must be set before it is populated, changing the format after it has data in it does not guarantee the correct totals.

I’m running Office 2000 Pro, so it might be different for the newer versions, or there might be an option somewhere that has an impact of this situation that I’m unaware of.MIKE

And I, running 2003 Pro, was able to change Dog to 1 and all 4 functions returned 61. Now, I have certainly seen cells be stuck as text, which is why I suggested a PasteSpecial...Add of zero. That has always solved the issue.

Never, ever, have I seen a PasteSpecial...Add of zero turn a 36 into 036.

Never, ever, have I seen a PasteSpecial...Add of zero turn a 36 into 036Haven't figured out how he did that trick...

MIKE

OK, more confusion...

My son has Office 2007, so I tried the same experiment on his machine and his reacts the same as mine.If cell A1 is formatted as TEXT, simply inputting the number 1, does not modify the cell format, it remains TEXT and the formulas that report a #VALUE error now total correctly and the others are one short.

When I change the format to NUMBERS nothing changes, except I get the small yellow exclamation box telling me that there is a possible error in cell A1 and gives me the usual options.

Once I select "Convert to Number" all totals are correct and the format has been changed to GENERAL.Very confusing....

MIKE

Just to close this thread out, I'm posting the solutions that I suggested via email. The cells that Brian was having problems with contained very long Concatenated IF statements.

Since the Concatenate function is going to force a cell to be text, the solution I suggested was to add a *1 at the end of each formula. After that the numbers would add up.

The other problem appeared to be that in some cases the IF statements returned a "". Excel does not always like to sum cells set to "", so I suggested that the "" be changed to 0 to ensure that the cells always contained a number.

I would like to thank those who helped me, particularly DerbyDad03, in resolving this issue. BrianW

For those that care, I thought I'd add that after further discussion, Brian has abandoned the Concatenated IF statements and is using VLOOKUP instead. VLOOKUP shortens the formulae required significantly and also returns "numbers" that can easily be summed - which was the original problem topic of this thread.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History