SUMIF with exact text string in an array as criteria

[gcefaloni]

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.)?


[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl75, width: 64"]Weights (A)[/TD]
[TD="class: xl73, width: 64"]Ratings (B)[/TD]
[/TR]
[TR]
[TD="class: xl77"] 3.700[/TD]
[TD="class: xl74"]AA-[/TD]
[/TR]
[TR]
[TD="class: xl80"] 1.100[/TD]
[TD="class: xl74"]BBB[/TD]
[/TR]
[TR]
[TD="class: xl77"] 0.444[/TD]
[TD="class: xl74"]A[/TD]
[/TR]
[TR]
[TD="class: xl77"] 1.100[/TD]
[TD="class: xl74"]A-[/TD]
[/TR]
[TR]
[TD="class: xl77"] 1.000[/TD]
[TD="class: xl74"]BBB[/TD]
[/TR]
[TR]
[TD="class: xl80"] 1.112[/TD]
[TD="class: xl74"]A+[/TD]
[/TR]
[TR]
[TD="class: xl77"] 0.600[/TD]
[TD="class: xl74"]A-[/TD]
[/TR]
[TR]
[TD="class: xl77"] 1.000[/TD]
[TD="class: xl74"]BBB+[/TD]
[/TR]
[TR]
[TD="class: xl79"] 1.000[/TD]
[TD="class: xl74"]BBB+[/TD]
[/TR]
[TR]
[TD="class: xl78"] 0.500[/TD]
[TD="class: xl76"]A+[/TD]
[/TR]
[TR]
[TD="class: xl81"] 0.770[/TD]
[TD="class: xl82"]AAA[/TD]
[/TR]
[TR]
[TD="class: xl81"] 0.905[/TD]
[TD="class: xl82"]AAA[/TD]
[/TR]
[TR]
[TD="class: xl77"] 0.750[/TD]
[TD="class: xl74"]BBB+[/TD]
[/TR]
[TR]
[TD="class: xl79"] 1.000[/TD]
[TD="class: xl74"]BBB[/TD]
[/TR]
[TR]
[TD="class: xl81"] 0.635[/TD]
[TD="class: xl82"]AAA[/TD]
[/TR]
[TR]
[TD="class: xl77"] 0.850[/TD]
[TD="class: xl74"]BBB+[/TD]
[/TR]
[TR]
[TD="class: xl77"] 0.500[/TD]
[TD="class: xl74"]AA-[/TD]
[/TR]
[TR]
[TD="class: xl77"] 0.500[/TD]
[TD="class: xl74"]BBB+[/TD]
[/TR]
[TR]
[TD="class: xl77"] 0.500[/TD]
[TD="class: xl74"]A-[/TD]
[/TR]
[TR]
[TD="class: xl78"] 0.500[/TD]
[TD="class: xl74"]A+[/TD]
[/TR]
[TR]
[TD="class: xl77"] 1.100[/TD]
[TD="class: xl74"]A+[/TD]
[/TR]
[TR]
[TD="class: xl77"] 0.500[/TD]
[TD="class: xl74"]AA-[/TD]
[/TR]
[TR]
[TD="class: xl77"] 0.250[/TD]
[TD="class: xl74"]A[/TD]
[/TR]
[TR]
[TD="class: xl77"] 0.225[/TD]
[TD="class: xl74"]A[/TD]
[/TR]
[TR]
[TD="class: xl79"] 0.500[/TD]
[TD="class: xl74"]BBB+[/TD]
[/TR]
[TR]
[TD="class: xl77"] 1.000[/TD]
[TD="class: xl74"]A+[/TD]
[/TR]
[TR]
[TD="class: xl81"] 0.500[/TD]
[TD="class: xl82"]AAA[/TD]
[/TR]
</tbody>[/TABLE]

 

[Tetra201]

Try

=SUM(SUMIF(B:B,{"A+","A","A-"},A:A))

 

[Phuoc]

Or try:

=SUMPRODUCT(SUMIF($B$2:$B$28,"A"&{"","+","-"},$A$2:$A$28))

 

[gcefaloni]

That's interesting, they both seem to work. The {} act as an OR function or some kind within the concatenate?

 

[gcefaloni]

Or try:

=SUMPRODUCT(SUMIF($B$2:$B$28,"A"&{"","+","-"},$A$2:$A$28))

Try

=SUM(SUMIF(B:B,{"A+","A","A-"},A:A))

That's interesting, they both seem to work. The {} act as an OR function or some kind within the concatenate? Also, why the doubling of SUM(SUMIF instead of just SUMIF?

 

[Tetra201]

The {"A+","A","A-"} is an array constant.

Without the SUM wrapper, SUMIF(B:B,{"A+","A","A-"},A:A) evaluates to an array of 4.212,0.919,2.2. SUM(4.212,0.919,2.2) gives the desired result.

In other words, SUM(SUMIF(B:B,{"A+","A","A-"},A:A)) is equivalent to SUM(SUMIF(B:B,"A+",A:A),SUMIF(B:B,"A",A:A),SUMIF(B:B,"A-",A:A))

Hope this helps.

 

[gcefaloni]

The {"A+","A","A-"} is an array constant.

Without the SUM wrapper, SUMIF(B:B,{"A+","A","A-"},A:A) evaluates to an array of 4.212,0.919,2.2. SUM(4.212,0.919,2.2) gives the desired result.

In other words, SUM(SUMIF(B:B,{"A+","A","A-"},A:A)) is equivalent to SUM(SUMIF(B:B,"A+",A:A),SUMIF(B:B,"A",A:A),SUMIF(B:B,"A-",A:A))

Hope this helps.

Thanks! Makes a lot more sense indeed.