Sum letters

I

Ingolf

Banned
Joined
Mar 20, 2011
Messages
809
Hi,

How can I get that RED sum.

[TABLE="width: 360"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A=10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B=10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C=10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]d=5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]D=20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AC=10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]A[/TD]
[TD]7[/TD]
[TD]B[/TD]
[TD]4[/TD]
[TD]C[/TD]
[TD]5[/TD]
[TD]---->[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]d[/TD]
[TD]A[/TD]
[TD]4[/TD]
[TD]AC[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]---->[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6[/TD]
[TD]d[/TD]
[TD]d[/TD]
[TD]2[/TD]
[TD]D[/TD]
[TD]2[/TD]
[TD]---->[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1[/TD]
[TD]C[/TD]
[TD]B[/TD]
[TD]3[/TD]
[TD]d[/TD]
[TD]D[/TD]
[TD]---->[/TD]
[TD]45[/TD]
[/TR]
</tbody><colgroup><col span="7"><col><col></colgroup>[/TABLE]
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Are you only wanting to sum up your letters?
If so, then I don't think your second and fourth examples are correct:
Code: 
[TABLE="class: cms_table, width: 360"]
<tbody>[TR]
[TD]3[/TD]
[TD]d[/TD]
[TD]A[/TD]
[TD]4[/TD]
[TD]AC[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]---->[/TD]
[TD][B][COLOR=#FF0000]40
[/COLOR][/B][/TD]
[/TR]
</tbody>[/TABLE]
5 +10 + 10 = 25

Code: 
[TABLE="class: cms_table, width: 360"]
<tbody>[TR]
[TD]C[/TD]
[TD]1[/TD]
[TD]C[/TD]
[TD]B[/TD]
[TD]3[/TD]
[TD]d[/TD]
[TD]D[/TD]
[TD]---->[/TD]
[TD][B][COLOR=#FF0000]45

[/COLOR][/B][/TD]
[/TR]
</tbody>[/TABLE]
10 + 10 + 10 + 5 + 20 = 55
Otherwise, I think you need to explain how these are supposed to work.

I would probably use VBA to create my own function to do this. Are you open to a VBA solution?
If so, are those values you are trying to add always in consecutive cells?
 
Upvote 0
Yes, you're right Joe4

I change letter in last minute and forget to calculate ....

Thanks for reply.
 
Upvote 0
....should be:

[TABLE="width: 268"]
<colgroup><col width="31" style="width: 23pt; mso-width-source: userset; mso-width-alt: 1133;" span="7"><col width="64" style="width: 48pt;"><col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"><tbody>[TR]
[TD="width: 31, bgcolor: transparent"][/TD]
[TD="width: 31, bgcolor: transparent"][/TD]
[TD="width: 31, bgcolor: transparent"][/TD]
[TD="width: 31, bgcolor: transparent"][/TD]
[TD="width: 31, bgcolor: transparent"][/TD]
[TD="width: 31, bgcolor: transparent"][/TD]
[TD="width: 31, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 79, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"]A=10[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"]B=10[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"]C=10[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"]d=5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"]D=20[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl82, bgcolor: transparent"]AC=10[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: transparent"]A[/TD]
[TD="class: xl79, bgcolor: transparent"]A[/TD]
[TD="class: xl79, bgcolor: transparent"]7[/TD]
[TD="class: xl79, bgcolor: transparent"]B[/TD]
[TD="class: xl80, bgcolor: transparent"]4[/TD]
[TD="class: xl79, bgcolor: transparent"]C[/TD]
[TD="class: xl80, bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"]40[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: transparent"]3[/TD]
[TD="class: xl81, bgcolor: transparent"]d[/TD]
[TD="class: xl81, bgcolor: transparent"]A[/TD]
[TD="class: xl80, bgcolor: transparent"]4[/TD]
[TD="class: xl81, bgcolor: transparent"]AC[/TD]
[TD="class: xl80, bgcolor: transparent"]5[/TD]
[TD="class: xl80, bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"]25[/TD]
[/TR]
[TR]
[TD="class: xl80, bgcolor: transparent"]6[/TD]
[TD="class: xl80, bgcolor: transparent"]6[/TD]
[TD="class: xl81, bgcolor: transparent"]d[/TD]
[TD="class: xl81, bgcolor: transparent"]d[/TD]
[TD="class: xl80, bgcolor: transparent"]2[/TD]
[TD="class: xl81, bgcolor: transparent"]D[/TD]
[TD="class: xl80, bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"] 30[/TD]
[/TR]
[TR]
[TD="class: xl81, bgcolor: transparent"]C[/TD]
[TD="class: xl80, bgcolor: transparent"]1[/TD]
[TD="class: xl81, bgcolor: transparent"]C[/TD]
[TD="class: xl81, bgcolor: transparent"]B[/TD]
[TD="class: xl80, bgcolor: transparent"]3[/TD]
[TD="class: xl81, bgcolor: transparent"]d[/TD]
[TD="class: xl81, bgcolor: transparent"]D[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl83, bgcolor: transparent"]55[/TD]
[/TR]
</tbody>[/TABLE]



Thanks for reply.
 
Upvote 0
You still haven't answered my questions at the end of my last post:
I would probably use VBA to create my own function to do this. Are you open to a VBA solution?
If so, are those values you are trying to add always in consecutive cells?
Click to expand...
 
Upvote 0
Sorry Joe4, don't read carefully.

I would probably use VBA to create my own function to do this. Are you open to a VBA solution?
Click to expand...
1. yes for VBA
If so, are those values you are trying to add always in consecutive cells?
Click to expand...
2. in that row could be "blank" cells. Blank as result of formula or not, it's depends.

Thanks
 
Upvote 0
@Ingolf;

If the data is in the range A1:G1 then you can use the following array formula (Ctrl+Shift+Enter);

Code: 
=SUM(((EXACT(A1:G1,"A"))+(EXACT(A1:G1,"B"))+(EXACT(A1:G1,"C"))+(EXACT(A1:G1,"AC")))*10 + (EXACT(A1:G1,"d"))*5 + (EXACT(A1:G1,"D"))*20)
 
Last edited:
Upvote 0
Haluk said:
@Ingolf;

If the data is in the range A1:G1 then you can use the following array formula (Ctrl+Shift+Enter);

Code: 
=SUM(((EXACT(A1:G1,"A"))+(EXACT(A1:G1,"B"))+(EXACT(A1:G1,"C"))+(EXACT(A1:G1,"AC")))*10 + (EXACT(A1:G1,"d"))*5 + (EXACT(A1:G1,"D"))*20)
Click to expand...


Haluk.

Tested. OK.
Thank you.

I came up with this array formula (Ctrl+Shift+Enter):

PHP: 
=SUM(COUNTIF(A1:G1,{"A","B","C","AC"})*10, EXACT(A1:G1,"d")*5, EXACT(A1:G1,"D")*20)
If someone need...

If someone can came up with NON array formula it will be great.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
If Formula
Replies
1
Views
98
DanteAmor
DanteAmor
L
  • Solved
Combinations of 3 from 16 numbers pool with unique product
2
Replies
14
Views
659
libelouli
L
A
  • Solved
Calculate monthly booked percentage of hotel rooms
2 3
Replies
21
Views
342
arendberg
A
Z
  • Solved
Look up to return name
Replies
4
Views
90
Peter_SSs
Peter_SSs
GMC The Macro Man
  • Solved
SUM COUNTIFS with AND + OR
Replies
3
Views
191
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top