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.

190919 - screenshot.png

190919 - screenshot 1.png

Find more posts tagged with

Manufacturing

Formulas

Comments

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,

Sign in to join the conversation!

Quick Links

Trending discussions

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…