Adding up letter values

[George]

Excel 2007;
I am looking for help with a formula that would let me add the values of
letters in a cell. ie. cells A1 - A4 have the letters A or B or C or D, the
letter A = 1 point, the letter B = 2 points, the letter C = 3 points, and the
letter D = 4 points. In cell A7 I need a total of all the points - in this
case the total would be 10

 

[Mike H]

Try this

=SUMPRODUCT(CODE(UPPER(A1:A4))-64)

Mike

 

[George]

Sorry Mike, bad example, in reality the letter A = 5 points, the letter B = 4
points, the letter C = 3 points, the letters D - H would equal 2 points each
and the letters I - M would equal 1 point each.

 

[Bob Phillips]

=SUMPRODUCT(LOOKUP(A1:A4,{"A","B","C","D","E","F","G","H"},{5,4,3,2,2,2,2,2}))

 

[Pete_UK]

One way would be to put this table somewhere in your sheet (eg
L1:M13):

A 5
B 4
C 3
D 2
E 2
F 2
G 2
H 2
I 1
J 1
K 1
L 1
M 1

Then in B1 you can put this formula:

=IF(A1="","",VLOOKUP(A1,L$1:M$13,2))

and copy down as required. Then just sum column B to get the total.

Hope this helps.

Pete

 

[Mike H]

Maybe

=SUMPRODUCT(LOOKUP({"A","B","C","D","H","I","M"},A1:A7,{5,4,3,2,2,1,1}))
Mike

 

[Mike H]

OOPS

=SUMPRODUCT(LOOKUP(A1:A7,{"A","B","C","D","H","I","M"},{5,4,3,2,2,1,1}))

Mike

 

[George]

Thanks Bob, but any of these letters can change cells, ie. I may want to
enter the letter A (worth 5 points) in a different cell, not always the first
cell. So where ever A is entered 5 points, and where ever G is entered 2
points.

 

[Mike H]

George,

If you try Bob's formula or the identical one I posted then you will find
that it works as you require.

The formula looks in the specified range (A1:A7) and if it finds an A in any
cell it evaluates that as 5 etc

Mike

 

[Bob Phillips]

Which is what it does!

 

[George]

Mike, thanks again, I pluged in the formula and it works. Next problem, some
of the cells will be blank, when these cells are left blank, it does not
total.

 

[Mike H]

George,

Instead of drip feeding your question why don't you post it all in one go?
If we don't understand then we'll ask.

Try this

=SUMPRODUCT(LOOKUP(A1:A7,{"A","B","C","D","H","I","M",0},{5,4,3,2,2,1,1,0}))

Mike