# SUMIF with exact text string in an array as criteria

#### gcefaloni

##### Board Regular
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.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>

### 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.

#### Tetra201

##### MrExcel MVP
Try

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

#### Phuoc

##### Board Regular
Or try:

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

#### gcefaloni

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

#### gcefaloni

##### Board Regular

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

Replies
3
Views
509
Replies
12
Views
739
Replies
6
Views
333