Excel: Counting Letters as numbers

[oliviaF]

I'm trying to sum across a row of a table, where some of the entries are letters which relate to numbers i.e. A =10, B = 20 etc.

The table of A=10,B=20 is located on a new sheet in the worksheet.

The result I want is sum(a1:a3) = 15 + A +2 = 27.

Does anyone know a suitable way to map the table of letters into the sum.

Thanks.

 

[klmorgan]

Use Vlookup to find the values in spares columns anad add those.

The source data you have on the other sheet needs to be sorted on the alpha character and named as range

Regards

Keith

 

[oliviaF]

Thanks Keith.


Do you know VLookup can be incorporated into a sum function? This is as some of the letters are interspersed thoughout the numbers, not in one set row or column

I am working with a table such as the one below

A 4 3
B B 7
7 6 8

and need to sum across rows, so if A = 10 and B= 20
Row 1 = 10+4+3 =17 and Row 2 = 20+20+7 = 47

MShelp looks like it can only be used for a single cell, for a single value.

 

[klmorgan]

I thought that they were all alpha.


You would need to have an if statement:

=IF(ISNUMBER(C8)=FALSE,VLOOKUP(C8,test,2,FALSE),C8)

Would check C8 and if it is a number cet the cell to that number or if its not look it up in range test

You would need to copy this into as many cells as you have entries then sum those cells

Keith

 

[KenWright]

Is thje A=10, B=20 really a logical set of numbers such as that? If so then you should be able to build a formula using SUMPRODUCT for example that does all the conversions, eg:-

=SUMPRODUCT((ISTEXT(A3:H3))*(CODE(A3:H3)-64))+SUM(A3:H3)

would give you A=1, B=2, C=3, D=4 etc

You could then easily amend the formula to

=SUMPRODUCT((ISTEXT(A3:H3))*(CODE(A3:H3)-64))*10+SUM(A3:H3)

which gives A=10, B=20, C=30 etc

All depends on your number series though.

Regards
Ken....................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

Another option is to have a Lookup type table and use SUMIF contained within SUMPRODUCT, eg assuming it is in K9:L11

K L
-------------
09 A 5
10 B 3
11 C 8

then using the same ranges as in my other example:-

=SUMPRODUCT(SUMIF($K$9:$K$11,A3:H3,$L$9:$L$11))+SUM(A3:H3)

The SUMPRODUCT and SUMIF do all the number conversions/matching, and the last SUM simply tallys up the real numbers.

Limited testing but seems to work on my test data.

Regards
Ken.....................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

In case it wasn't that clear, the range of data being summed was assumed to be in A3:H3 eg

A B C D E F G H I
1
2
3 4 8 A 4 C 2 C 9 Formula
4 C 5 2 A A C 3 B Formula copy
5 A A 2 B 5 2 B 3 Formula copy
6 A B 2 C 3 B 3 B Formula copy

and then assuming your range of values for your letters is in say K9:L11

K L
-------------
09 A 5
10 B 3
11 C 8

then in say cell I3 above put the following formula:-

=SUMPRODUCT(SUMIF($K$9:$K$11,A3:H3,$L$9:$L$11))+SUM(A3:H3)

then just copy that cell down to the other rows below.

The SUMPRODUCT and SUMIF do all the number conversions/matching, and the last SUM simply tallys up the real numbers.

Regards
Ken....................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------