Forum Discussion

lilian51129's avatar
lilian51129
Copper Contributor
Dec 21, 2022
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...
excel
Formulas and Functions
  • MY's avatar
    MY
    Dec 22, 2022

    (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).

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Dec 22, 2022

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".