SUMIF with exact text string in an array as criteria

gcefaloni

Board Regular
Joined
Mar 15, 2016
Messages
114
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.)?


Weights (A)Ratings (B)
3.700AA-
1.100BBB
0.444A
1.100A-
1.000BBB
1.112A+
0.600A-
1.000BBB+
1.000BBB+
0.500A+
0.770AAA
0.905AAA
0.750BBB+
1.000BBB
0.635AAA
0.850BBB+
0.500AA-
0.500BBB+
0.500A-
0.500A+
1.100A+
0.500AA-
0.250A
0.225A
0.500BBB+
1.000A+
0.500AAA

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

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Phuoc

Board Regular
Joined
Apr 29, 2016
Messages
209
Or try:

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

gcefaloni

Board Regular
Joined
Mar 15, 2016
Messages
114
That's interesting, they both seem to work. The {} act as an OR function or some kind within the concatenate?
 

gcefaloni

Board Regular
Joined
Mar 15, 2016
Messages
114

ADVERTISEMENT

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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,577
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.
 
Last edited:

gcefaloni

Board Regular
Joined
Mar 15, 2016
Messages
114
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,790
Messages
5,524,893
Members
409,609
Latest member
Channingz

This Week's Hot Topics

Top