Hi,

I'm trying to make an excel workbook that calculates various values and
statistics for games of scrabble. One of the things I want the sheet to
do is to automatically update the sum total for the points for a given
set of letters. For instance, if I type into cell A1 the letters "BAT"
I want cell B1 to output "5", by knowing that the letters have the
following values and summing them: B=3, A=1, T=1. I've tried using the
COUNTIF() function:

=SUM((COUNTIF(A1,"*"&{"A","B","C","D","E","F","G", "H","I",
"J","K","L","M","N","O","P","Q","R","S","T",
"U","V","W","X","Y","Z"}&"*")0)*{1,3,3,2,1,
4,2,4,1,8,5,1,3,1,1,3,10,1,1,1,1,4,4,8,4,10})

however the function only seems to count unique letter values, and
cannot handle words with two or more instances of one letter. For
example, the word "letter" is given a value of "4", but should be "6",
however only one "t" and one "e" are counted. Any suggestions?

Thanks!

Here's something to get you pointed in the right direction.....

Put this table in G1:H26
A 1
B 3
C 3
D 2
E 1
F 4
G 2
H 4
I 1
J 8
K 5
L 1
M 3
N 1
O 1
P 3
Q 10
R 1
S 1
T 1
U 1
V 4
W 4
X 8
Y 4
Z 10

Then....for a word in A1

This formula calculates its scrabble value
B1:
=SUMPRODUCT(LOOKUP(MID(A1,ROW($A$1:INDEX($A:$A,LEN (A1),1)),1),$G$1:$G$26,$H$1:$H$26))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

Hi,

I'm trying to make an excel workbook that calculates various values and
statistics for games of scrabble. One of the things I want the sheet to
do is to automatically update the sum total for the points for a given
set of letters. For instance, if I type into cell A1 the letters "BAT"
I want cell B1 to output "5", by knowing that the letters have the
following values and summing them: B=3, A=1, T=1. I've tried using the
COUNTIF() function:

=SUM((COUNTIF(A1,"*"&{"A","B","C","D","E","F","G", "H","I",
"J","K","L","M","N","O","P","Q","R","S","T",
"U","V","W","X","Y","Z"}&"*")0)*{1,3,3,2,1,
4,2,4,1,8,5,1,3,1,1,3,10,1,1,1,1,4,4,8,4,10})

however the function only seems to count unique letter values, and
cannot handle words with two or more instances of one letter. For
example, the word "letter" is given a value of "4", but should be "6",
however only one "t" and one "e" are counted. Any suggestions?

Thanks!

Thanks, Ron. We've been trying all day to figure this one out. Happy
New Year.

Regards,

eric

Ron Coderre wrote:
Here's something to get you pointed in the right direction.....

Put this table in G1:H26
A 1
B 3
C 3
D 2
E 1
F 4
G 2
H 4
I 1
J 8
K 5
L 1
M 3
N 1
O 1
P 3
Q 10
R 1
S 1
T 1
U 1
V 4
W 4
X 8
Y 4
Z 10

Then....for a word in A1

This formula calculates its scrabble value
B1:
=SUMPRODUCT(LOOKUP(MID(A1,ROW($A$1:INDEX($A:$A,LEN (A1),1)),1),$G$1:$G$26,$H$1:$H$26))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

Hi,

I'm trying to make an excel workbook that calculates various values and
statistics for games of scrabble. One of the things I want the sheet to
do is to automatically update the sum total for the points for a given
set of letters. For instance, if I type into cell A1 the letters "BAT"
I want cell B1 to output "5", by knowing that the letters have the
following values and summing them: B=3, A=1, T=1. I've tried using the
COUNTIF() function:

=SUM((COUNTIF(A1,"*"&{"A","B","C","D","E","F","G", "H","I",
"J","K","L","M","N","O","P","Q","R","S","T",
"U","V","W","X","Y","Z"}&"*")0)*{1,3,3,2,1,
4,2,4,1,8,5,1,3,1,1,3,10,1,1,1,1,4,4,8,4,10})

however the function only seems to count unique letter values, and
cannot handle words with two or more instances of one letter. For
example, the word "letter" is given a value of "4", but should be "6",
however only one "t" and one "e" are counted. Any suggestions?

Thanks!