excel 2007

[Bluesword]

Can I use the HLOOKUP formula for this...?
C3:AB4 is the data range. C3:AB3 is the top row and C4:AB4 is the bottom row. I want my formula and result in cell U7.
I am using the cells C7:T7 as my data entry range, where I can only enter a single digit uppercase letter. Each letter correspond to a numerical value, ex.,A=6, B=12, C=18 ect,.
If I type ABC, then I should get a value of 36 in cell U7.

I want a formula that will given me the sum of those numbers as stated. How can this be done?

 

[xld]

How about this array formula

=SUM(IF(C7:T7<>"",LOOKUP(C7:T7,C3:AB3,C4:AB4)))

 

[Bluesword]

I got the #VALUE error.

 

[c_m_s_jr]

Did you hit control+shift+enter after putting in the formula? That will wrap it in {} which are necessary in the formula xld gave.

 

[Bluesword]

great! ...Thanks, now it works.