# Sum values in a row

Instead of using several vloopup formulas and adding them up to get a value, is there an Offset or Match Function that will look at a text in a cell and convert it to it's corresponding value and sum it all up in one cell.

Example
H = 9
M = 5
L = 2

A1         B1           C1            D1             E1
H           M             L               M             21

The total for this A - D is 9+5+2+5 = 21
###### Who is Participating?

Commented:
Or if those are the only 3 characters, then you can avoid the side table with formula like:

=SUMPRODUCT(LOOKUP(A1:D1,{"H","L","M"},{9,2,5}))

again, first array must be in ascend. alpha. order
0

Commented:
If you list the Letters in ascending alphabetic order on the side somewhere, with the corresponding values in the next column, then you can use something like:

=SUMPRODUCT(LOOKUP(A1:D1,\$M\$1:\$M\$3,\$N\$1:\$N\$3))

where

where M1:N3 contains the table of values with column M in ascending alpha order.
0

Author Commented:
That's exactly what I was looking for.  Thank you
0

Older than dirtCommented:
Here is a User Defined Function that will behave just like a normal function.

Place =SumLetters(A1:D1) in E1 and copy down.
``````Function SumLetters(r As Range) As Integer

Dim intValue(65 To 90) As Integer
Dim lngCol As Long

' Uncomment other letters as needed and replace the question
' mark with their values
'intValue(65) = ? 'A
'intValue(66) = ? 'B
'intValue(67) = ? 'C
'intValue(68) = ? 'D
'intValue(69) = ? 'E
'intValue(70) = ? 'F
'intValue(71) = ? 'G
intValue(72) = 9 'H
'intValue(73) = ? 'I
'intValue(74) = ? 'J
'intValue(75) = ? 'K
intValue(76) = 2 'L
intValue(77) = 5 'M
'intValue(78) = ? 'N
'intValue(79) = ? 'O
'intValue(80) = ? 'P
'intValue(81) = ? 'Q
'intValue(8?) = ? 'R
'intValue(83) = ? 'S
'intValue(84) = ? 'T
'intValue(85) = ? 'U
'intValue(86) = ? 'V
'intValue(87) = ? 'W
'intValue(88) = ? 'X
'intValue(89) = ? 'Y
'intValue(90) = ? 'Z

For lngCol = r.Column To r.Column + r.Columns.Count - 1
SumLetters = SumLetters + intValue(Asc(Cells(r.Row, lngCol)))
Next
End Function
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.