Update: I ended up using the following formula in the Requirement Met? column.
IF(COUNT(INDIRECT("F"&MATCH(B2,B:B,0):INDIRECT("F"&MATCH(B2,B:B,1)))=COUNTA(INDIRECT("F"&MATCH(B2,B:B,0):INDIRECT("F"&MATCH(B2,B:B,1))),"Met","Not Met")
I have a list of requirements which may be verified by one or more documents. I'm trying to find a way for Excel to evaluate whether or not a requirement is met by looking at all of the relevant documents' release dates. For example, if a requirement has only one verifying document and that document has been released, that requirement can be considered to be met. However, if a requirement has three related documents, and only 2 have been released, the requirement has not yet been met.
| Group | Requirement | Verifying Document | Document Number | Release Date | Requirement Met? |
|---|---|---|---|---|---|
| 1 | 1-1235 | 79K85956 Summary Report for Modifications | 79K85956 | 12/13/2020 | Yes |
| 1 | 1-7412 | 79K13345 Test Report for Materials | 79K13345 | 6/14/2019 | Yes |
| 1 | 1-961 | 79K32121 Purchase Order for Supplier | 79K32121 | 12/13/2017 | Yes |
| 2 | 2-123 | Laboratory A Certification 79K21314 | 79K21314 | 5/11/2016 | No |
| 2 | 2-123 | Laboratory B Certification 79K21315 | 79K21315 | 6/14/2019 | No |
| 2 | 2-123 | Laboratory C Certification 79K21316 | 79K21316 | No |
Ultimately, I would like to determine how many requirements are met by each group in each month, but I think I can do that easily enough with a PivotTable if I can just figure out a formula for the "Requirement Met?" column.
Previously, I stumbled into a formula that does something similar in another situation, but can't figure out how to make it work this time. In that case, I was looking at multiple rows grouped by the Change Notice column and looking for a key word in the Workflow Step Name column.
I used =IF(COUNTIF(INDIRECT("C"&MATCH(B2,B:B,0)):INDIRECT("C"&MATCH(B2,B:B,1)),"Yes"),"No","Yes") for the Clean Release? column.
| Simplified Workflow Step Name | Role w/o NA | Workflow Step Name | Change Notice | Release Status | Role | User | Action Taken | Time In | Time Out | Clean Release? |
|---|---|---|---|---|---|---|---|---|---|---|
| Configuration Review | Configuration Reviewer | Engineering Document Release Configuration Review | CN00035 | Released | Configuration Reviewer | smitha | Rejected | 5/4/2016 | 5/5/2016 | No |
| Back Fix from CR | Author | Incorporate Conformance Comments | CN00035 | Released | N/A | williamss | Completed | 5/5/2016 | 5/12/2016 | No |
CodePudding user response：
I tried this in Google Sheets, but I think it should work the same in Excel:
=IF(COUNTBLANK(FILTER($E:$E, $A:$A=$A1))=0,"Yes","No")
Columns A and E are the ones corresponding to Group and Release Date.
It's basically counting how many blank cells are in Release Date but only for the rows that have the same value in Group than the current row (row 1 in my example, of course you need to expand it to all rows), and then prints "Yes" only if that number is 0, "No" otherwise.