Sum letters

[Ingolf]

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]

 

[Dave Patton]

=COUNTIF(A1:G1,{"A","B","C","AC"})*10+SUMPRODUCT(--(EXACT(A1:G4,"d")))*5+SUMPRODUCT(--(EXACT(A1:G4,"D")))*20

 

[Dave Patton]

=SUMPRODUCT(--(COUNTIF(A1:G1,{"A","B","C","AC"})))*10+SUMPRODUCT(--(EXACT(A1:G1,"d")))*5+SUMPRODUCT(--(EXACT(A1:G1,"D")))*20

or

=SUMPRODUCT(--(COUNTIF(A1:G4,{"A","B","C","AC"})))*10+SUMPRODUCT(--(EXACT(A1:G4,"d")))*5+SUMPRODUCT(--(EXACT(A1:G4,"D")))*20

 

[Tetra201]

Try

=SUMPRODUCT(EXACT(A1:G1,{"A";"B";"C";"d";"D";"AC"})*{10;10;10;5;20;10})

 

[Haluk]

Another alternative is;

You can revise my suggested formula in messages No.8 and No.9 by replacing SUM with SUMPRODUCT for a non-array formula.

 

[Ingolf]

=COUNTIF(A1:G1,{"A","B","C","AC"})*10+SUMPRODUCT(--(EXACT(A1:G4,"d")))*5+SUMPRODUCT(--(EXACT(A1:G4,"D")))*20

Dave,

Thanks for reply.
Your formula gave incorrect result (even if I change range A1:G4 with A1:G1, in EXACT formula.)

=SUMPRODUCT(--(COUNTIF(A1:G1,{"A","B","C","AC"})))*10+SUMPRODUCT(--(EXACT(A1:G1,"d")))*5+SUMPRODUCT(--(EXACT(A1:G1,"D")))*20

This formula is OK.

 

[Ingolf]

Try

=SUMPRODUCT(EXACT(A1:G1,{"A";"B";"C";"d";"D";"AC"})*{10;10;10;5;20;10})

Wow, excellent formula, very nice. Thank you.

 

[Joe4]

Looks like you got lots of good solutions, so I won't bother with a VBA solution.

 

[Joe4]

OK, because you requested to see a VBA solution via PM, here is a User Defined Function that you can create in VBA and use.

Function MySum(rng As Range) As Long

    Dim cell As Range
    Dim stotal As Long
    
    For Each cell In rng
        Select Case cell
            Case "A", "B", "C", "AC"
                stotal = stotal + 10
            Case "d"
                stotal = stotal + 5
            Case "D"
                stotal = stotal + 20
        End Select
    Next cell
    
    MySum = stotal
    
End Function

So, you would then just use it like any other Excel function on your spreadsheet, i.e.
=MySum(A1:G1)

 

[Ingolf]

Thank you Joe4.
Thanks to all.