Audra Breedlove
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's exactly what I was looking for. Thank you
Here is a User Defined Function that will behave just like a normal function.
Place =SumLetters(A1:D1) in E1 and copy down.
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
=SUMPRODUCT(LOOKUP(A1:D1,$
where
where M1:N3 contains the table of values with column M in ascending alpha order.