Forum Discussion

Jna3276's avatar
Jna3276
Copper Contributor
Aug 27, 2024
Solved

Formula Excel Help

Hi,   I'd like to create a formula which looks at column F and if it contains words, "Jay", "Em" then to go to column B AND C and if they both state 'Complete' or 'NA' then to return words in colum...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Aug 28, 2024

    Jna3276 

    Try this formula:

     

    =IF(OR([@[Name]]={"Jay","Em"}), IF(SUM(COUNTIF(Table1[@[Servicing Status]:[Complaint Status]], {"Complete","NA"}))=2, "Case Closed", "Case Open"), "")

     

    (Change Name to the real name of that column)

Jna3276's avatar
Jna3276
Copper Contributor
Sep 09, 2024
I'd like to add word withdrawn to the list, if this is found in both or either Servicing / Complaint Status then the return values should be Case closed, like it does for Complete or NA
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 09, 2024

    Jna3276 

    Thanks. Since you're still looking at 2 columns, you should use =2, not =3:

     

    =IF(OR([@[Name]]={"Jay","Em"}), IF(SUM(COUNTIF(Table1[@[Servicing Status]:[Complaint Status]], {"Complete","NA","Withdrawn"}))=2, "Case Closed", "Case Open"), "")
     

Resources