Can you trigger an automation or conditional formatting based on grouped cells/same cell value?
Answers
-
@SNickNBCUniUSH that's great!
Breaking it down...
The first COUTIF is looking at the full Building column and counting it if it is equal to the value in building@row
COUNTIF([building]:[building],[building]@row)
In the second piece. The Collect is pulling a set of values from the building column where the value in that column is equal to the value in building at row, and each device column is equal to yes.
COLLECT([building]:[building],[building]:[building],[building]@row, [device 1]:[device 1], "Yes", [device 2]:[device 2], "Yes", [device 3]:[device 3], "Yes")
The Count() around the collect gives you how many values came back in the collect.
If all rows have "Yes" for all three devices these the COUNTIF(), and COUNT() above will return the same number. So you can set your IF() to look for the difference between the COUNTIF() and COUNT(). If they are the same (i.e., difference = 0) then you know all the rows met your requirement. So you set your value if true to "Yes", and your value if false to "No"
So in my example.
- Rows with Building 3225 would get COUNTIF of 2, and COUNT of 1. Since 2-1 <> 0, the returned result is "No"
- Rows with Building 3243 would get COUNTIF of 5 and COUNT of 5. Since 5-5 = 0, the returned result is "Yes"
I hope that makes sense.
-
@sharkasits super helpful. Thank you!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!