Hi Pamela,
With a tip of the cap to my colleague Tony (aka @Anthony Messam‚) who noodled this one out a few years ago:
- SUM(1,SUB(LEN(REPLACE({Checkbox Field},",","**")),LEN({Checkbox Field})))
- Just replace the two occurrences of "Checkbox Field" with the name of the selection field you'd like to count and you should be good to go.
The concept is to compare the length of the multi-select values with the commas between each selected value vs without the commas, thereby deducing the number of selections (plus one).
I'd also developed a similar technique a that to allow clients to vote on feature requests in a multi select, weighting each by each client’s relative ARR. When the feature request came in (as an Issue) the dropdown of “Client Votes” would allow agents to “check off” which clients voted in favor of the feature, with each client having its own weighting (e.g. Client A = 10K, B = 30K, C = 25K, etc.), then grouped the issues by the weighting of the votes.
Either way, one gotcha (as I recall) is that if everything gets unchecked, sometimes the last known calculated value sticks around, so I think we worked around it by adding a “None” option, so there’d always be (in theory) at least one value to calculate against...which you might then need to trap in the formula to consider None = zero, from a count perspective.
Regards,
Doug
