Forum Discussion
Solved
if multiple cells contain specific text then return value
I have a column that can show two different statuses, Approved and Amend Required. I want to find a formula that will give me two options: - if all my cells have the value Approved then I want to ha...
(edited to account for empty state)
Try:
=IF(COUNTIF(range,""),"",IF(PRODUCT(--(range="APPROVED")),"APPROVED","AMEND REQUIRED"))Basically assigns binary to your cells where APPROVED = 1 and anything else = 0. These are all multiplied together and if the result is 1 it'll be "APPROVED" otherwise "AMEND REQUIRED".
With this, it considers all 3 possible states: all approved, all amend (or empty), mixed approved and amend (or empty).
oh I see, so these columns will be filled in the course of a week therefore when the status cells are empty is there any way the Overall Status to be empty as well before all the departments have put their approval?
lilian51129That's exactly what this formula does. See attached.
- Apologies you are right. If those cells had the value '0' and were autopopulated from other tabs how would this formula work?
lilian51129 Try this then:
=IF(SUM(--(B3:B6=""))<>0,"",IF(COUNTIF(B3:B6,"Amend Required")<>0,"Amend Required","Approved"))This will return a blank as long as not all four cells have something other than "" in them.