SUMIFS looking for specific phrase
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.
Comments

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)

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))

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,
Help Article Resources
Categories
Check out the Formula Handbook template!