Sum values in a row

Posted on 2015-01-05
Medium Priority
Last Modified: 2015-01-05
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.

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
Question by:ablove3
  • 2
LVL 23

Expert Comment

ID: 40531908
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:



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

Accepted Solution

NBVC earned 2000 total points
ID: 40531913
Or if those are the only 3 characters, then you can avoid the side table with formula like:


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

Author Closing Comment

ID: 40531964
That's exactly what I was looking for.  Thank you
LVL 51

Expert Comment

by:Martin Liss
ID: 40532005
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)))
End Function

Open in new window


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

594 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question