Forum Discussion
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 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)
Sorry couldn't create a separate post.
I have a transaction sheet that calculates income from E33 cells from other sheets. =SUM('Invoice 1:Invoice 20'!E33) is normally working, but I have one sheet that is not cooperating and I get the #REF error and cant figure it out. I have checked cells, sheet, calculation...
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 ClosedOh wait! You wrote NA and I used N/A. Sorry about that.
=IF(OR([@Name]={"Jay", "Em"}), IF(OR(AND([@[Servicing Status]]:[@[Complaint Status]]="Complete"), AND([@[Servicing Status]]:[@[Complaint Status]]="NA"))), "Case Closed", "Case Open"), "")
