"+" as a string

[suiz919]

Hi-

I'm trying to do a simple sumif formula for s&p ratings such as AA+ and AA-, but when i'm using it in the formula I think excel is confusing the "+" as an excel function.

Below is the formula i've put together. RATING_AVAIL is a ratings array which contains various s&p ratings. and MVAI is the market value array. I'm trying to take a sum market values where rating_avail = "AA+" or "AA" or "AA-"

=SUM(IF((RATING_AVAIL="AA+")+(RATING_AVAIL="AA")+(RATING_AVAIL="AA-",MVAI,0),0)))


Thank you in advance

 

[CWatts]

You can try CHAR(43) for the + but it shoudl be fine since it's enclosed in quotes.

Edit: Looking at your formula, you're trying to add true or false statements. Are you trying to add all three of them like a =SUMIF?

Such as:

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">31</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Rating</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Number</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">32</TD><TD style="TEXT-ALIGN: center">AA+</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">33</TD><TD style="TEXT-ALIGN: center">AA</TD><TD style="TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">34</TD><TD style="TEXT-ALIGN: center">AA-</TD><TD style="TEXT-ALIGN: center">5</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">35</TD><TD> </TD><TD style="TEXT-ALIGN: center">12</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E35</TD><TD>=SUMIF(D:D,"AA+",E:E)+SUMIF(D:D,"AA",E:E)+SUMIF(D:D,"AA-",E:E)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[Scott Huish]

Try this:

=SUM(SUMIF(RATING_AVAIL,{"AA+","AA","AA-"},MVAI))

 

[suiz919]

thank you! CWATTS and HotPepper. They worked