Sum if following criteria match

[bjelinski]

Dear all ,

In the attachment you can find a dummy example of my problem ,that I want to simplify. I need to sum the amounts that are in column A , but based on the ratings (column B). On the right side you have the criteria bins. For example sum all values in column A if they are rated AA, AA- , AA+. Or sum values from column A if they are rated BBB;BBB+;BBB-;Baa. So the formula should sum only values that have this criteria and will place the sum in the appropriate bins on the right.

I have done this assignment by doing filtering and then summing up rating by rating but wanted to see how can I use formula in solving this.

Thanks for help,

Bjelinski

[NBVC]

Does this work?

Please Login or Register  to view this content.

adjust ranges to suit your data.

[bjelinski]

Just tried the formula, but it doesnt give me the right amounts, dont really understand why? because it looks a little bit complicated...

[NBVC]

As far as my testing went.. they all resulted in correct sums...

The only thing I neglected to tell you was to change the last Rating in F7 to N R to be consistent with your column B entries...

[bjelinski]

NBVC,

it works now. It is very sensitive to spaces , so i fixed it a little bit and did a check up by summing the values. They are equal. I am adding the solution, for others to use it for revision.

Thanks!

Take Care!

[NBVC]

I would have suggested that clean up.. but usually I am met with the response, that "I don't have control over that setup"... so I basically worked around it...

But if you do clean it up to that point, then we can get rid of a the Substitute() functions and reduce it to:

=SUMPRODUCT(--(ISNUMBER(SEARCH(" "&$B$2:$B$68&" "," "&TRIM(F2)&" "))),$A$2:$A$68)