Can you trigger an automation or conditional formatting based on grouped cells/same cell value?

2»

Answers

  • sharkasits
    sharkasits ✭✭✭✭✭

    @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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!