Forum Discussion
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 column D with 'Case Closed'
If column F does not contain 'Jay' or 'Em' then return a blank
If one of the two Column B and C contain any other words then column D should return words 'Case Open'
Hope that all makes sense. Is this possible?
At the moment I have a formula which only looks at column B and C, this is the forumla with the actual column table headers:
=IF(AND([@[Servicing Status]]="Complete", [@[Complaint Status]]="Complete"), "Case closed", "Case Open")
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)
Let's say column F has header Name.
=IF(OR([@Name]={"Jay", "Em"}), IF(OR(AND([@[Servicing Status]]:[@[Complaint Status]]="Complete"), AND([@[Servicing Status]]:[@[Complaint Status]]="N/A"))), "Case Closed", "Case Open"), "")
- Thank you for this.
Something doesn't seem right as it's only returning Case Open, no Case Closed. I should have many Case ClosedWhat do you want to return if column B has "Complete" and column C has "N/A"? And what if column B has "N/A" and column C has "Complete"?
