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
- Sheets
- Sheets, Dashboards
- Reports
- Sheets – Scale
- Sheets
- 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:
- A way for the string match to not be an exact match
- 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.