Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 108
  • Last Modified:

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
0
ablove3
Asked:
ablove3
  • 2
1 Solution
 
NBVCCommented:
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
 
NBVCCommented:
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
 
ablove3Author Commented:
That's exactly what I was looking for.  Thank you
0
 
Martin LissOlder 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

Open in new window

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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now