Best Of

Hi @henry florez

I'd be happy to help! However the formula will need to know what you would define as the "overall health". For example, does that mean whatever colour has the majority of rows? Or if there are only Green and Red rows, would that be a Yellow health?

Here's a version of an overall formula. See if this gives you the type of summary you're looking for:

=IF(COUNTIF(Health:Health, "Red") >= (COUNTIF(Health:Health, "Yellow") + COUNTIF(Health:Health, "Green")), "Red", IF(COUNTIF(Health:Health, "Yellow") >= (COUNTIF(Health:Health, "Red") + COUNTIF(Health:Health, "Green")), "Yellow", IF((COUNTIF(Health:Health, "Red") + COUNTIF(Health:Health, "Yellow")) >= COUNTIF(Health:Health, "Green"), "Yellow", "Green")))

I'll break down what it says:

IF(COUNTIF(Health:Health, "Red") >= (COUNTIF(Health:Health, "Yellow") + COUNTIF(Health:Health, "Green")), "Red",

^ If the COUNT of Red rows is greater-than or equal-to the total number of both Yellow and Green rows, return a Red overall ball.

IF(COUNTIF(Health:Health, "Yellow") >= (COUNTIF(Health:Health, "Red") + COUNTIF(Health:Health, "Green")), "Yellow",

Same thing, but for yellow. If there are more Yellow rows than Red and Green combined, return a Yellow overall status ball.

IF((COUNTIF(Health:Health, "Red") + COUNTIF(Health:Health, "Yellow")) >= COUNTIF(Health:Health, "Green"), "Yellow",

Another Yellow statement, but this time we're looking to see if there are more Red and Yellow balls than Green balls. This could mean there are just 2 red, 2 yellow, and 2 green - then it should return YELLOW instead of Green.

I also added an = sign in there to default it to yellow if there's a tie:

"Green")))

Otherwise, it will return a Green status.

Let me know if these parameters work for you, or if you have a different definition for each overall colour!

Cheers,

Genevieve

Hi @batomlinson1

If it is OK to create a separate sheet with the options, here is a solution that uses the JOIN, COLLECT, HAS, and CHAR functions.

=JOIN(
  COLLECT(
    {Multi Dropdown List}, 
      {Multi Dropdown List}, 
        AND(
          HAS([Column2]@row, @cell), 
          NOT(HAS([Column3]@row, @cell))
          )
        ),
   CHAR(10))

The above formula references the {Multi Dropdown List} column range shown below, and first collects the range whose values are in Column 2 but not in Column 3, then joins the value with CHAR(10) to create a multiple-dropdown list cell value.

The JOIN text operation is possible because MULTI_PLICLIST is added to the TEXT_NUMBER column type in version 2. So, it is internally a TEXT_NUMBER but behaves as a MULTI_PICKLIST by setting the column to version 2 and applying the proper format, in this case, delimited by CHAR(10).

https://developers.smartsheet.com/api/smartsheet/openapi/columns/column

versionnumber

0: CONTACT_LIST, PICKLIST, or TEXT_NUMBER.

1: MULTI_CONTACT_LIST.

2: MULTI_PICKLIST.

Enum 0 1 2

Option Sheet

https://app.smartsheet.com/b/publish?EQBCT=d061cfdb116f43d7b16d45efa1abf6ee

Look Up Sheet

{Multi Dropdown List} range