My coworker Anthony and I were working on sorting out the prioritization of enhancements. In order to properly count the number of elements within a Multi-Select, CONTAINS() won’t do it right. Below, I will walk through the thought process.

In a data set, where you are trying to count enhancement data and you have 2 sheets, you would start with building a metric.

Column Name: Enhancement

1. Sheets
2. Sheets, Dashboards
3. Reports
4. Sheets – Scale
5. Sheets
6. Sheets

….

You may be tempted to start off with a simple count.

=COUNTIF({Enhancement}, "Sheets")

You quickly recognize, however, as your product gets more and more robust, “Sheets” is no longer specific enough. Also, the column is Multi-Select Dropdown, so if we write our formula like above, we will fail. This is because Smartsheet will attempt to do a string match.

In our above example, the formula will return 3. We want it to return 4.

We need to add:

1. A way for the string match to not be an exact match
2. A way for the data to be accurate for the “Sheets” label

You may be tempted to implement:

=COUNTIF({Enhancement}, CONTAINS("Sheets", @cell"))

However, this would produce 5. Why? Because our data contains one element: “Sheets – Scale” would falsely set off our flag because this specifically, this element contains “Sheets”. Thus, our formula would add it.

The correct implementation would be:

=COUNTIF({Enhancement}, HAS(@cell, "Sheets"))

This would correctly produce 4. The reason is that the HAS() formula looks at either a value or a range. You need to call the cell itself as a range into the HAS() formula. If you call an array (i.e. Multi-Select Dropdown cell within Smartsheet with multiple elements), Smartsheet will intelligently consider this a range. Within this range, it will search for “Sheets” and correctly flag the answer. Note that HAS() also produces a boolean, so it would return true for this count comparison. This is what “@cell = 5” or other comparators in COUNTIF() is doing any time you are attempting to evaluate a range.