Hi guys,
I have a question regarding the best way to do a sumif of a range containing an exact string which in my example is a single letter. The problem I'm encountering is that this letter also happens to be within words of other cells. How would I go about returning only the sum of cells with the exact letter as a text string. I am trying to sum weights in column A where in column B there are credit ratings of Bonds (e.g. "A+, A-, A, AA+, AA-, AA, AAA, BBB, BBB-) and want to create bins where I can add up all the weights for A+, A, A-; or AA+, AA, AA-; BBB=, BBB, BBB-; etc. As previously mentioned, the issue is if I search for just A*, it will give me the sum of all the rows that include an "A", including AA, AAA, A+, without distinction. So how do I sum for only the same credit rating categories (sumif includes A+,A, A- but exclude AA, AAA, etc.)?
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>
I have a question regarding the best way to do a sumif of a range containing an exact string which in my example is a single letter. The problem I'm encountering is that this letter also happens to be within words of other cells. How would I go about returning only the sum of cells with the exact letter as a text string. I am trying to sum weights in column A where in column B there are credit ratings of Bonds (e.g. "A+, A-, A, AA+, AA-, AA, AAA, BBB, BBB-) and want to create bins where I can add up all the weights for A+, A, A-; or AA+, AA, AA-; BBB=, BBB, BBB-; etc. As previously mentioned, the issue is if I search for just A*, it will give me the sum of all the rows that include an "A", including AA, AAA, A+, without distinction. So how do I sum for only the same credit rating categories (sumif includes A+,A, A- but exclude AA, AAA, etc.)?
Weights (A) | Ratings (B) |
3.700 | AA- |
1.100 | BBB |
0.444 | A |
1.100 | A- |
1.000 | BBB |
1.112 | A+ |
0.600 | A- |
1.000 | BBB+ |
1.000 | BBB+ |
0.500 | A+ |
0.770 | AAA |
0.905 | AAA |
0.750 | BBB+ |
1.000 | BBB |
0.635 | AAA |
0.850 | BBB+ |
0.500 | AA- |
0.500 | BBB+ |
0.500 | A- |
0.500 | A+ |
1.100 | A+ |
0.500 | AA- |
0.250 | A |
0.225 | A |
0.500 | BBB+ |
1.000 | A+ |
0.500 | AAA |
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>