Assign the alphabets

[royst]

Dear Friends,

Now I'ave a new problem and it is as same as my previous one (adding the numbers only). But this time I'm going to calculate all of them. Before that,, here is the code:

A = 1, B = 2, C = 3, D = 4, E = 5, F = 6. (nothing else, untill F only)

3 3 B 1 = 3+3+B(2)+1 = 9

4 0 C A = 4+0+C(3)+A(1) = 8

How am I going to assign the A,B,C,D,E and F to the numbers I want to add. I'm not expert in excell, so please help me. Thank you.

[royUK]

A link to the previous question would be helpful

[Paul]

Hi Royst,

Without using a macro or user-defined function, you might try the following array formula (confirmed with CTRL+SHIFT+ENTER, not just ENTER):

=SUM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"A",1),"B",2),"C",3),"D",4),"E",5),"F",6),ROW(INDIRECT("1:"&LEN(A1))),1)+0)

[martindwilson]

Non array assuming you will only ever enter numbers or A,B,C,D,E,F
=sum(if(isnumber(a1),a1,code(upper(a1))-64),if(isnumber(b1),b1,code(upper(b1))-64),if(isnumber(c1),c1,code(upper(c1))-64),if(isnumber(d1),d1,code(upper(d1))-64))

[DonkeyOte]

It would seem from the prior post that the values are in separate cells

=SUM(A1:A4,COUNTIF(A1:A4,{"A","B","C","D","E","F"})*{1,2,3,4,5,6})

[martindwilson]

interesting use of countif

[royst]

Dear Friends,

My problems have solved and I'm closing my topics on "Sum of Integers", "Differentiate the Alphabets", "Adding the Numbers" and "Assign the Alphabets". All this thread's are related and the answers are found. I wish to thank's everyone, who involved in helping it. Mr. Pike, Mr.Zbor, Mr. Daddylonglegs, Mr. ChemistB, Mr. Excelkeechak, Mr. RoyUK, Mr. Paul, Mr. Martindwilson and Mr. DonkeyOte......
YOU GUYS ARE WONDERFUL and THANK YOU.. THANK YOU very much.
If I forget to mention anyone please forgive me. And I hope all of well and god's blessing always to you guys. I hope everyone could see this quote. THANK YOU n BYE.