Reputation: 77

Excel 2007 SUM() does not work on calculated columns

I have an excel sheet with 12 columns as below

7   6   5   4   3   2   7   6   5   4   3   2
5   3   0   0   0   0   0   7   6   2   4   9
35  18  0   0   0   0   0   42  30  8   12  18
5   8   0   0   0   0   0   2   0   8   2   8
0

I am multiplying each column in row 1 with the corresponding column in row 2, I get row 3 as result of this multiplication. Then I am choosing the number in unit's place from row 3 using right(row3,1) function and I get row 4. Then I am using SUM(A4:L4) to get the sum of all the columns in row 4, but I get the sum as 0, whereas the correct sum is 33. My question is will SUM(A4:L4) work on calculated columns? Please advise.
Thank you.

Upvotes: 0

Answers (3)

Jerry

Reputation: 71538

When use a string manipulation function, such as RIGHT, LEFT and MID, the result returned is a text value.

If you try to add up text values with SUM, it will be equivalent to be adding 0s and the end result would be obviously 0.

In exel it's quite easy to convert text to numbers, provided they can be converted to numbers, using the function VALUE() like CRondao suggested.

I personally prefer using *1 because any number multiplied by 1 remains one, but since you're using an operation on the text value, excel will convert it to number (unless it cannot be converted to a number, in which case, you get the #VALUE! error). So, I would do:

=RIGHT(A3,1)*1

And dragged across. You can also use +0, or /1 or -0 to get the same results.

Another option would be to extract a number directly, which would then be what pnuts suggested. MOD returns a number:

=MOD(A3,10)

And last, if you don't want to change the formula in the 3rd row, you can use something like the below using Ctrl+Shift+Enter:

=SUM(A4:L4*1)

Which does almost the same thing as multiplying by *1, since it first multiplies each cell by 1, then adds the results. The equivalent without Ctrl+Shift+Enter invocation would be to use SUMPRODUCT: