"SUMIF" situation with filtered data, but searching text strings

[sarahmck]

Howdy, friends! I'm looking for a different kind of SUMIF-filtered-data solution.

In one column I have long text strings which contain the criteria I need to sum.
In the next column, I have the amounts that I need to sum.

I have one criterion in the first column that identifies figures that need to be filtered out of the sum (in the example below, the text "FILTER").

Simplified example table:

Color	Amount
Blue_DEFABC	10
RedABCDEF	7
Blue-FILTER	8
PinkXYZ_ABC	6
OrangeXYZABC	10
BlueXYZABC	11
Green-FILTER	2
Green123	7

<tbody>
</tbody>

I need to be able to filter out the lines that contain "FILTER", and then subtotal the second column for the lines where the first column contains the text "Blue". (As well as the other colors, but of course I only need one example solution ) So I'd need Blue to return a sum of 21.

I've tried lots of solutions from this forum and around the web for SUMIFing filtered data, but none of them seem to work with text strings and wildcards.

Any help is much appreciated! I will feel so accomplished if I can get this working!

 

[MrBill11]

sarahmck, If blue is in column A, and filter is in column B, and amount is in column C you could use the following in C10 =SUMIF(A1:A8,"Blue",C1:C8)-SUMIFS(A1:C8,A1:A8,"Blue",B1:B8,"Filter")

 

[sarahmck]

Thanks, MrBill11! Unfortunately, it's not as clean as the example to be able to split out the text into columns. An actual example of one of the text strings is "N-A_Behavioral Targeting_ABC_BONUS_APR-MAR_040112-033113".

So the word "Behavioral" corresponds to the colors in my example, and "BONUS" is the word that marks lines that need to be filtered. I can't think of any way to do that other than to filter it.