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

Try this

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

Mike

"George" wrote:

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

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.

"Mike H" wrote:

Try this

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

Mike

"George" wrote:

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

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

--
__________________________________
HTH

Bob

"George" wrote in message
...
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.

"Mike H" wrote:

Try this

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

Mike

"George" wrote:

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

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.

"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"George" wrote in message
...
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.

"Mike H" wrote:

Try this

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

Mike

"George" wrote:

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

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

"George" wrote:

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.

"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"George" wrote in message
...
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.

"Mike H" wrote:

Try this

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

Mike

"George" wrote:

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

Which is what it does!

--
__________________________________
HTH

Bob

"George" wrote in message
...
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.

"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"George" wrote in message
...
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.

"Mike H" wrote:

Try this

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

Mike

"George" wrote:

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

Maybe

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

"George" wrote:

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.

"Mike H" wrote:

Try this

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

Mike

"George" wrote:

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

OOPS

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

Mike

"Mike H" wrote:

Maybe

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

"George" wrote:

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.

"Mike H" wrote:

Try this

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

Mike

"George" wrote:

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, 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" wrote:

OOPS

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

Mike

"Mike H" wrote:

Maybe

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

"George" wrote:

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.

"Mike H" wrote:

Try this

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

Mike

"George" wrote:

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

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

On Jan 30, 12:39*pm, George wrote:
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.



"Mike H" wrote:
Try this

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

Mike

"George" wrote:

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- Hide quoted text -

- Show quoted text -