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 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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!