SUMPRODUCT function for two arrays. Array 1 contains text

[Payal]

i have one array A2 to A10 containing text (A+/A/B/C). another array C2 to
C10 contains numbers (0/1). i want to multiply each element of one array with
another, replacing A+ for 2, A for 1.5, B for 1 and C for .5. Example:
A B C
----------------------
A 0
A+ 1
B 0
C 1
...........................
it should give me the result: 2.5 [(replace A by 1.5*0) + (replace A+ by
2*1) + (replace B by 1*0) + (replace C by .5*1)]

Please help me with this..

Regards

 

[Rick Rothstein \(MVP - VB\)]

Either of these two array-entered** formulas seem to do what you want...

=SUM(B2:B10*IF(A2:A10="",0,2.5-MATCH(A2:A10,{"A+","A","B","C"},0)/2))

=SUM(B2:B10*IF(A2:A10="",0,LOOKUP(A2:A10,{"A","A+","B","C"},{1.5,2,1,0.5})))

** Commit the formula by using Ctrl+Shift+Enter, not just Enter by itself.

Rick