In Column A, B and C I have raw data, I need to first find if a particular file with particular date and particular status is available in raw data or not, if it is not available it can come with answer 0 or Dash. And if it is available I need to add logics to get the correct answer.
- If a particular File with particular Date is available and if it is meeting the criteria in Column C it can return with the answer "Available"
The Criteria is In column C for that particular file if it founds the word "Combine" and/or "Part 1 and Part 2" and/or "Part 2 and Combine" than it should return with answer "Available"
- For that particular file in Column C if it founds only "Part 1" or only "Part 2" it should display with answer "Not available"
This way i can get if both parts of a particular file is available or not. please see the screenshot
I hope i am able to clarify the question. I will be thankful for this.
| Date | Name of File | Status of File | |||
|---|---|---|---|---|---|
| 20-Jan-23 | North | Combine | |||
| 20-Jan-23 | South | Combine | |||
| 20-Jan-23 | East | Combine | |||
| 20-Jan-23 | West | Combine | |||
| 20-Jan-23 | Alpha | Combine | |||
| 20-Jan-23 | Beta | Combine | |||
| 20-Jan-23 | Gama | Combine | |||
| 21-Jan-23 | North | Part 1 | |||
| 21-Jan-23 | North | Part 2 | |||
| 21-Jan-23 | South | Part 1 | |||
| 21-Jan-23 | East | Part 2 | |||
| 21-Jan-23 | East | Combine | |||
| 21-Jan-23 | West | Part 1 | |||
| 21-Jan-23 | Alpha | Part 1 | |||
| 21-Jan-23 | Beta | Part 1 | |||
| 21-Jan-23 | Beta | Part 2 | |||
| 21-Jan-23 | Gama | Combine |
CodePudding user response：
You can use this function in G2:
=IF(SUMPRODUCT(($A$2:$A$18=E2)*($B$2:$B$18=F2)*($C$2:$C$18 = "Combine")),"Available",
IF(COUNTIFS($A$2:$A$18,E2,$B$2:$B$18,F2,$C$2:$C$18,"Part *")=2,"Available","not available"))
