+ Reply to Thread
Results 1 to 2 of 2

Summing up columns noob

  1. #1
    Registered User
    Join Date

    Arrow 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.


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

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

  2. #2
    Forum Expert swatsp0p's Avatar
    Join Date
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    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.


    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

    The older I get, the better I used to be.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1