Using CONTAINS for 2 criteria

PSames

I am having a problem creating a formula using contains for 2 criteria. Here is my formula that pulls the information I need.

=SUMIFS([Column2]1:[Column2]22, [Primary Column]1:[Primary Column]22, CONTAINS("hat", @cell))

I want to sum column 2 if the primary column contains "hat" and "gloves". I get various errors when I try to add "gloves". Can contain be used for 2 different criteria?

Additional question - can you use CONTAIN with <> so it returns only entries that does not have that text string in column?

Thanks for any help with this.

PSames

Genevieve and Paul,

Thank you for all of your help with this. I was able to get the formula to work with using SUM with the SUMIFS. Here is the formula I used to total all rows that had either hats or gloves in the primary column.

=SUM(SUMIFS([Column2]1:[Column2]24, [Primary Column]1:[Primary Column]24, CONTAINS("hat", @cell)), SUMIFS([Column2]1:[Column2]24, [Primary Column]1:[Primary Column]24, CONTAINS("gloves", @cell)))

Then to exclude certain colors here is the formula that worked.

=SUM([Column2]1:[Column2]24, -(SUM(SUMIFS([Column2]1:[Column2]24, [Primary Column]1:[Primary Column]24, CONTAINS("blue", @cell)), SUMIFS([Column2]1:[Column2]24, [Primary Column]1:[Primary Column]24, CONTAINS("green", @cell)))))

I appreciate your explanation and will go back and use your suggestions to shorten the formulas.

This community is awesome. Although I do not post many questions I use the search function to find answers regularly.

Hope you have a great day!

Patty

Genevieve P.

Hi @PSames

You're correct: you would not be able to add two different CONTAINS in that formula. You could build out multiple SUMIFS formulas that all add together, using the + sign, each with its own Contains criteria... but then you will get duplicate counts as it will read an entry that has both "hat" and "gloves" in the same cell as two different entries instead of 1. 

If I have misunderstood how your Primary Column is set up, it would be useful to see a screen capture of how your sheet is set up to see if there are other possibilities for what you're looking to do.

In regards to your second question, you could use NOT with CONTAINS to Sum all of the cells that do NOT contain a specific criteria... for example:

=SUMIFS([Column2]:[Column2], [Primary Column]:[Primary Column], NOT(CONTAINS("shoes", @cell)))

Cheers!

Genevieve

Paul Newcome

@PSames You can use an AND function wrapped around multiple sets of criteria for the same range. I do this quite often to keep the more complex formulas organized.

=SUMIFS([Column2]1:[Column2]22, [Primary Column]1:[Primary Column]22, AND(CONTAINS("hat", @cell), CONTAINS("gloves", @cell)))

I do agree with @Genevieve P.'s response to your second question regarding the NOT function though.

Genevieve P.

@Paul Newcome of course!!

However, for the AND formula, that would only Sum the cells that contain both "hat" and "gloves", correct? But it would not count any cells that only have "hat" or only "gloves".

You could use OR instead of AND:

=SUMIFS([Column2]1:[Column2]22, [Primary Column]1:[Primary Column]22, OR(CONTAINS("hat", @cell), CONTAINS("gloves", @cell)))

@PSames can you outline exactly when you would like it to SUM?

Paul Newcome

@Genevieve P.

That was my intent. To only sum those cells that contain both. I came to this by the following portion of the original post:

"I want to sum column 2 if the primary column contains "hat" and "gloves"."

I agree though that if the poster would like to sum for any cell containing "hat" or any cell containing "gloves" the OR function would work. I just saw the "and" and went with it.

PSames

Genevieve and Paul,

Thank you for all of your help with this. I was able to get the formula to work with using SUM with the SUMIFS. Here is the formula I used to total all rows that had either hats or gloves in the primary column.

=SUM(SUMIFS([Column2]1:[Column2]24, [Primary Column]1:[Primary Column]24, CONTAINS("hat", @cell)), SUMIFS([Column2]1:[Column2]24, [Primary Column]1:[Primary Column]24, CONTAINS("gloves", @cell)))

Then to exclude certain colors here is the formula that worked.

=SUM([Column2]1:[Column2]24, -(SUM(SUMIFS([Column2]1:[Column2]24, [Primary Column]1:[Primary Column]24, CONTAINS("blue", @cell)), SUMIFS([Column2]1:[Column2]24, [Primary Column]1:[Primary Column]24, CONTAINS("green", @cell)))))

I appreciate your explanation and will go back and use your suggestions to shorten the formulas.

This community is awesome. Although I do not post many questions I use the search function to find answers regularly.

Hope you have a great day!

Patty

PSames

@Genevieve P., @Paul Newcome

I realized after I posted my reply I did not tag either of you.

Thanks again for the help with this.

Paul Newcome

@PSames Happy to help! Glad you were able to figure out a working solution. 👍️

Based on your latest response, it looks like @Genevieve P.'s solution using the OR function would be the more efficient version of counting your hats or gloves.

To make your color exclusion a little more efficient, you will want a combination of Genevieve's NOT(CONTAINS(........)) and my AND.

=SUMIFS([Column2]1:[Column2]22, [Primary Column]1:[Primary Column]22, AND(NOT(CONTAINS("blue", @cell)), NOT(CONTAINS("green", @cell))))

Chris Jernigan

I have a similar issue.

I want to total the duration of all tasks based on Work Type if they are not complete.

I have a duration column, Work Type column, state column.

Complexitites:

1. The Work Type column could have multiple types
2. I do not need to include the duration of completed tasks (IE exclude that task)

Maybe I am close

=SUMIFS([Duration]:[Duration], [Work Type]:[Work Type], (CONTAINS("SQL")), [State]:[State], NOT(CONTAINS("Complete")))

Chris Jernigan

Reading through some other threads I think I answered my own question.

I needed the @cell reference.

This is working for me now.

=SUMIFS(Duration:Duration, [Work Type]:[Work Type], (CONTAINS("SQL", @cell)), State:State, AND(NOT(CONTAINS("Complete", @cell))))

Genevieve P.

Glad you figured it out, @Chris Jernigan!