Total Column wise problem

[AVG123]

hello friends.........


1. I have two col, Col-A have A,B,C,D…………Y, Z and Col-B have 1,2,3,4………..25,26
Type DAC in Col-C
Then it sum the value of DAC, D have 4, C have 3, A have 1, i.e. D=4, A=1, C=3=4+1+3=8 should be the total in Col-D
If I type ABCDEFGHIJKLMNOPQRSTUVWXYZ=Total should be (Sum the value 1to 26)=351

2. If I type 1234567 in a column, I want the total of all numbers i.e. 28 should be the total in this example

[pjwhitfield]

When you say "Type DAC in Col_C" do you mean that cell any cell in column C (C1 or C6 or C17346 for example) could equal "DAC" or is it one letter per cell ie C1 = D, C2 = A, C34 = C? etc

If you could provide a sample workbook with expected results that would be ideal.

[AVG123]

TEST.jpg please see the screenshot

[romperstomper]

Sum of the letters in C1:

=SUMPRODUCT(CODE(MID(C1,ROW(INDIRECT("1:"&LEN(C1))),1))-64)

Sum of the digits in C2:

=SUMPRODUCT(--(MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1)))

Is that what you mean?

[samba_ravi]

Please attach a sample excel file with expected result
if the above is the case you can use below formula

Please Login or Register  to view this content.

[AVG123]

If i type DAAC, it is giving me 8 although it should be 9,
DAAC contains A 2times, it calculate A one time

[pjwhitfield]

Is it possible to have a combination of letters and numbers? eg "DAC12A"?

[AVG123]

No sir.....

[pjwhitfield]

I have a VBA solution (which also works for combined letters and numbers although that isn't required)

Please Login or Register  to view this content.

[samba_ravi]

OK Try any one of below formula

Please Login or Register  to view this content.

or
=SUMPRODUCT(LOOKUP(MID($C$1,ROW(INDIRECT("1:"&LEN($C$1))),1),$A$1:$A$26,$B$1:$B$26))

[AVG123]

Thankyou very much sir...........problem solved...........

[romperstomper]

If i type DAAC, it is giving me 8 although it should be 9,
DAAC contains A 2times, it calculate A one time

Mine calculates 9 for that.