# MATCHing an ARRAY within a SUMPRODUCT

#### gcefaloni

##### Board Regular
Hi,

I'm trying to calculate a weighted credit score for the credit ratings I have in my bonds portfolio. The first table represents the different S&P credit ratings (column A) and their arbitrary scores (B).
The second table represents the bonds I have, the investment in each bond (C), their respective credit ratings (D which refers to A) and I use an index match function looking through the first table to get (E) the respective score of each bond. I want to do a weighted average of credit scores so that I can know what is the average credit rating of my entire portfolio. In order to do that, I use sumproduct of column C and column E divided by sum of column C (=22.541) which gives me an average score of 76.5443. I want to be able to do that using an array so that I do not have to do the intermediate step of creating column E.

Is it possible to do something like this? (I know it doesn't work but that's essentially what I'd like to do to skip column E calculations that gets the score line by line.
=SUMPRODUCT(\$C\$2:\$C\$28,INDEX(\$A\$2:\$B\$22,MATCH(D2:D28,\$A\$2:\$A\$22,0),2))/SUM(\$C\$2:\$C\$28)

These are the two tables I'm working with:

 S&P (A) Score (B) AAA 100 AA+ 95 AA 90 AA- 85 A+ 80 A 75 A- 70 BBB+ 65 BBB 60 BBB- 55 BB+ 50 BB 45 BB- 40 B+ 35 B 30 B- 25 CCC+ 20 CCC+ 15 CCC- 10 CC 5 NR 0

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

 Investment (C) S&P Rating (D) Score (E) 3.7 AA- 85 1.1 BBB 60 0.444 A 75 1.1 A- 70 1 BBB 60 1.112 A+ 80 0.6 A- 70 1 BBB+ 65 1 BBB+ 65 0.5 A+ 80 0.77 AAA 100 0.905 AAA 100 0.75 BBB+ 65 1 BBB 60 0.635 AAA 100 0.85 BBB+ 65 0.5 AA- 85 0.5 BBB+ 65 0.5 A- 70 0.5 A+ 80 1.1 A+ 80 0.5 AA- 85 0.25 A 75 0.225 A 75 0.5 BBB+ 65 1 A+ 80 0.5 AAA 100 22.541 76.5443

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

<colgroup><col span="4"></colgroup><tbody>
</tbody>

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### pgc01

##### MrExcel MVP
Hi

Try in J3:

=SUMPRODUCT(D2:D28,SUMIF(A2:A22,E2:E28,B2:B22))/SUM(D2:D28)

Last edited:

#### gcefaloni

##### Board Regular
Hi

Try in J3:

=SUMPRODUCT(D2:D28,SUMIF(A2:A22,E2:E28,B2:B22))/SUM(D2:D28)

ABCDEFGHIJK
1S&P (A)Score (B) Investment (C)S&P Rating (D)
2AAA100 3.7AA- Result76.5443
3AA+95 1.1BBB
4AA90 0.444A
5AA-85 1.1A-
6A+80 1BBB
7A75 1.112A+
8A-70 0.6A-
9BBB+65 1BBB+
10BBB60 1BBB+
11BBB-55 0.5A+
12BB+50 0.77AAA
13BB45 0.905AAA
14BB-40 0.75BBB+
15B+35 1BBB
16B30 0.635AAA
17B-25 0.85BBB+
18CCC+20 0.5AA-
19CCC+15 0.5BBB+
20CCC-10 0.5A-
21CC5 0.5A+
22NR0 1.1A+
23 0.5AA-
24 0.25A
25 0.225A
26 0.5BBB+
27 1A+
28 0.5AAA
29
[Book1]Sheet1

<tbody>
</tbody>

Works like a charm. Thanks a lot! It is very appreciated.

#### pgc01

##### MrExcel MVP
You're welcome. Thanks for the feedback.

Replies
6
Views
136
Replies
6
Views
387
Replies
3
Views
152
Replies
14
Views
162
Replies
6
Views
124