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

Book2.xlsx27 KB

excel

Formulas and Functions

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

Brass Contributor

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

lilian51129
Copper Contributor
Dec 23, 2022
Thanks this worked like a charm!

Forum Discussion

if multiple cells contain specific text then return value

Resources