IF Text in any field between multiple columns, then equals text value

I am having some issues using the "IF" formula. I'm trying to make it so if ANY VALUE is in the "CUT, FAB, WELD" columns then it will output the text "In Progress" to the "STATUS" Column, and if there is no value in any of the columns then the "STATUS" column will say "Not Started". Along with this, I have a checkbox in the "COMPLETE" Column, and if that is checked then I would like the status to say "Complete" I've tried multiple different formulas but i cant seem to get it.

Sheet

=IF(CUT@row:WELD@row = 0, "Not Started", "In Progress")

Find more posts tagged with

Manufacturing

Formulas

Comments

Mike Wilday

Try something like this:

=IF(Complete@row =1, "Complete", IF(AND(CUT@row = "", FAB@row ="", WELD@row = "", WRAP@row = ""), "Not Started", "In Progress")

codynew

Wow, thank you that worked great! One more thing, can you add an argument to it where only if a value in the column "Part" then it shows "Not Started?"

Right now the whole table is showing "Not Started" even if there isn't anything on that row.

Mike Wilday

Try this: It's checking to see if part is blank and returning blank, if so...

=IF(Part@row = "", "", IF(Complete@row =1, "Complete", IF(AND(CUT@row = "", FAB@row ="", WELD@row= "", WRAP@row = ""), "Not Started", "In Progress")))

codynew

Thanks so much!

Mike Wilday

You're welcome.

Paul Newcome

As another option (if you plan on adding or removing columns in the future) you could replace the AND with a COUNTIFS statement. Something like...

=IF(Part@row = "", "", IF(Complete@row =1, "Complete", IF(COUNTIFS(CUT@row:WRAP@row, ISTEXT(@cell))= 0, "Not Started", "In Progress")))

What this does is looks across the range from your Cut column to your Wrap column and counts how many cells have text in them. If the result is 0 (meaning all are blank) then it will display "Not Started".

The only real difference between this and Mike's solution is that if you need to add or remove a column between Cut and Wrap, you won't have to adjust your formula.

The downside is that if you rearrange the columns it will look at EVERYTHING between Cut and Wrap. So moving the Fab column to the left of Cut or to the right of Wrap will essentially remove the Fab column from the formula.

Mike Wilday

Neat solution, Paul. And a simple way to check the whole range.

Sign in to join the conversation!

Quick Links

Trending discussions

A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?
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…