Roll up of assessment data with conditions
I need to be able to roll up associated transmittal #s to 1 Transmittal and if the assessment status is Not Compliant for any of the Compliance Assessments then Transmittal 1 should equal Not Compliant, Transmittal 2 = Not Compliant, Transmittal 3= Compliant
Answers

Hi Jessica,
I presume this is a crosssheet rollup, is that correct?
You can use a COUNTIFS formula to see if there are rows that contains a certain Transmittal number and the status "Not Compliant". If the COUNT is greater or equal to 1, you can have it return text.
Here's the COUNTIFS portion, where you list each column and the criteria:
COUNTIFS({Transmittal # column}, "TRN00001", {Assessment Status column}, "Not Compliant")
Here's the full formula, embedded in an IF statement:
=IF(COUNTIFS({Transmittal # column}, "TRN00001", {Assessment Status column}, "Not Compliant") >=1, "Not Compliant", "Compliant")
Then for each Transmittal number you just need to swap out the text "In these" to be the new number. Does that make sense? Let me know if I can clarify any of this further!
Cheers,
Genevieve
Help Article Resources
Categories
Check out the Formula Handbook template!