# Summing up columns noob

1. ## Summing up columns noob

Hey all need to get a sum of a couple of columns in excel 2003 but the catch is if one field has a letter it needs to use a predefined value.

IE:

90
C
A <--- Sum this column use 75 for C 85 for B and 95 for A
90
------

i need a general formula some columns might not have letter grades some might ONLY have letter grades  Register To Reply

2. Hi. You can't change the value of a cell (in that cell). You can, however, use a helper column to do this calculation for you.

First you need to set up a table that explains the value and it's equivalent. In this case, on another sheet (or in an unused area of the sheet you are working in) in one column enter "a", "b" and "c" and in the next column enter the equivalents of 95, 85, and 75. Name this range "values".

Now, insert a column next to your original data and enter this formula in the top cell of the range:

=IF(ISNUMBER(A1),A1,VLOOKUP(A1,values,2,0)) assumes your data is in A1:Ann, enter this in B1 and copy down to Bnn.

At the end of this column, do a =Sum(B1:Bnn)

NOTE: instead of the lookup function and creating the table, you could use nested IF statements e.g.

=IF(A1="a",95,IF(A1="b",85,IF(A1="c",75,A1)))

The drawback of this is that if, for some reason, you need to change the equivilency values (say a=96), you would have to edit *every* formula in **every** cell. With the table, you simply change the one cell.

Good Luck

Bruce  Register To Reply