Forum Discussion
Formula Excel Help
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)
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)
- 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
Please explain in more detail what you want.
- If I add another criteria to NA, Complete should I amend the formula to =3?
=IF(OR([@[Name]]={"Jay","Em"}), IF(SUM(COUNTIF(Table1[@[Servicing Status]:[Complaint Status]], {"Complete","NA"}))=2, "Case Closed", "Case Open"), "") =IF(OR(C2={"em","jay"}), IF(SUM(COUNTIF(A2:B2, {"complete","NA"}))=2,"case closed", "in progress"), "")
The formula first checks whether the analyst column contains either em or jay. If so, it evaluates the IF(SUM(...) part, otherwise it returns an empty string.
COUNTIF(A2:B2, {"complete","NA"}) returns 2 values: the number of cells in A2:B2 that contain complete, and the number of cells that contain NA.
SUM(COUNTIF(A2:B2, {"complete","NA"})) adds these results together. If the sum is 2, A2 and B2 both contain complete or NA. In that case, we return case closed. If the sum is less than 2, at least one of the cells does not contain complete or NA, so we return in progress.
- Hi, are you able to explain the formula and how this has worked?
- Ah that's great! I must have typed the formula in wrong!! Thanks so much for your help :c)
The formula that I proposed appears to work:
HansVogelaar hi, I've attached a file, the last column shows what the expected outcome should be. Hope that makes sense
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
- Hi that hasn't worked, both columns are marked as complete but the returned value is case open.
I excluded Table1 from the formula