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

sarahmck

New Member
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!

Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

MrBill11

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

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

Replies
3
Views
85
Replies
5
Views
202
Replies
0
Views
155
Replies
9
Views
180
Replies
1
Views
167

1,196,021
Messages
6,012,904
Members
441,740
Latest member
Latrs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back