using <> and = in one formula

[suiz919]

here i want to take the sum of market values when multiple conditions are met.

here are the conditions:

CRS_4 <>"cash" or "cash fund"
and RATING_AVAIL = "AA+", "AA", or "AA-"

here is the formula i've come up with, but it's giving me a 0 which is not correct.
MVAI is an array i have for market value

=SUM(IF((CRS_4<>"CASH")*(CRS_4<>"CASH FUND"),IF(RATING_AVAIL="AA+",IF(RATING_AVAIL="AA",IF(RATING_AVAIL="AA-",MVAI,0),0))))

Thank you!

 

[Scott Huish]

Isn't this just an extension of the post here?

http://www.mrexcel.com/forum/showthread.php?t=582005

 

[Scott Huish]

Your formula will not work because all your conditions cannot be true at the same time.

 

[suiz919]

Hi HotPepper. This one is slightly different. I need multiple "not equal to" statements in this situation in addition to multiple "equal to" statements. I'm not sure how to do both in one formula.

Isn't this just an extension of the post here?

http://www.mrexcel.com/forum/showthread.php?t=582005

 

[suiz919]

HotPepper, i don't think that's right.

I want to take the sum when the security is not equal to "1" or "2" and where the rating is either "a","b", or "c".


=SUM(IF((CRS_4<>"CASH")*(CRS_4<>"CASH FUND"),IF(RATING_AVAIL="AA+",IF(RATING_AVAIL="AA",IF(RATING_AVAIL="AA-",MVAI,0),0))))

Your formula will not work because all your conditions cannot be true at the same time.

 

[rafaelspeixoto]

I think you messed up your logic in the rating part. If I understood you correctly, you are looking for this:

=SUM(IF((CRS_4<>"CASH")*(CRS_4<>"CASH FUND"), IF(RATING_AVAIL<>"AA+",IF(RATING_AVAIL<>"AA",IF(RATING_AVAIL<>"AA-",MVAI,0),0))))

as it would be impossible for the rating to be equal to "AA+", "AA" and "AA-" at the same time. You could also simplify this formula like this:

=SUM(IF((CRS_4<>"CASH")*(CRS_4<>"CASH FUND"), IF(left(RATING_AVAIL,2)<>"AA",MVAI,0),0))