SUMIFS looking for specific phrase

saltyblueocean

Hi Community,

I've been digging to find an answer for sometime now and haven't been able to find what I am looking for quite yet.

What I need my formula to do is to search for a particular phrase and then sum the values on that same row if that phrase is found. There are four columns in which that phrase could be found, and it will only be found in one. It is also cross referencing month and year, but I am only struggling with the phrase portion of the formula.

My current formula is:

=SUMIFS({Micro Data - In Process Range 4}, {Micro Data - In Process Range 4}, >0, {Micro Data - In Process Range 3}, $[September - Number of Positive Results (#1)]$1, {Micro Data - In Process Range 3}, $Year$1, {Micro Data - In Process Range 11}, Ingredient@row)

The problem is the way I have it built, I need a column for each possible alternative. Aka, identical formula that searches column number 1, then column number 2, etc. I would rather have it search multiple columns (in this case 4) and find the phrase I am looking for and then perform the rest of the formula.

Hoping this all makes sense! It's a complicated web I am weaving. If more details are needed, let me know.

 

Mike Wilday

If you can get it working for one column, just string your countifs together to count for each column and get a sum total. 

=Countifs(range, criterion)+countifs(range, criterion) + Countifs(range, criterion)

Paul Newcome

Mike's solution will definitely work. Use individual SUMIFS for each column and then add those SUMIFS together.

 

Another option would be to add a helper column to your source sheet where you join the text of all 4 of those columns together. You can then reference this joined column in your SUMIFS and look for that specific text string using a CONTAINS function.

 

=SUMIFS({Sum Range}, {Joined Column Range}, CONTAINS("specific text", @cell))

saltyblueocean

Paul, was super excited to use the CONTAINS function but not sure how to go about it. With contains doesn't it need to look at a range of cells? not just one cell? I tried typing it in since I already have a joined column. Wrote the below formula, I would prefer that "Apricot Puree" actually reference the contents of a cell, but no luck here using either method.

=SUMIFS({Micro Data - In Process Range 4}, CONTAINS("Apricot Puree", {Micro Data - In Process Range 13}), {Micro Data - In Process Range 3}, $[September - Number of Positive Results (#1)]$1, {Micro Data - In Process Range 2}, $Year$1)

Also perused your other postings on CONTAINS, hoping to become a future expert on this function so I can contribute later on.

Cheers,