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

codynew

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.

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

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.