Roll up of assessment data with conditions

Jessica Larkin

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

Genevieve P.

Hi Jessica,

I presume this is a cross-sheet 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}, "TRN-00001", {Assessment Status column}, "Not Compliant")

Here's the full formula, embedded in an IF statement:

=IF(COUNTIFS({Transmittal # column}, "TRN-00001", {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