Dec 21 2022 11:57 PM
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 have the value Approved returned and
- if one cell contains the value Amend Required and the rest are showing Approved I want to have the value Amend Required returned.
I tried the IF formula on the attached but it's not the one, anyone could help pls?
Dec 22 2022 12:26 AM - edited Dec 22 2022 12:26 AM
@lilian51129 I believe you need to use something like this:
=IF(COUNTA(B3:B6)=4,IF(COUNTIF(B3:B6,"Amend Required")<>0,"Amend Required","Approved"),"")
It will see is all fields are filled in before calculating the Overall Status. When that is the case it will look if there is any occurrence of "Amend Required".
Dec 22 2022 12:42 AM
Dec 22 2022 12:55 AM
@lilian51129That's exactly what this formula does. See attached.
Dec 22 2022 01:05 AM
Dec 22 2022 01:30 AM - edited Dec 22 2022 03:15 AM
Solution(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).
Dec 22 2022 01:56 AM
@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.
Dec 22 2022 10:22 PM
Dec 22 2022 01:30 AM - edited Dec 22 2022 03:15 AM
Solution(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).