COUNTALL(Multiple Options) always returns 1

[Sashman]

Expected behaviour: return the number of selected items.

[Alexander_Sorok]

That happens because we currently only support numbers, text and dates in the formulas. The multiple options get converted to a text string, so the count becomes 1. At some point we’ll support all types and there’ll definitely be a way to count the number of selected options.

[Simon_Brown · Accepted solution]

IF( LEN({FieldName}) = 0, 0, LEN(CONCATENATE(",", {FieldName})) - LEN(SUBSTITUTE({FieldName}, “,”, “”)))

As a workaround, this formula works for me, but none of the multiple options can have a “,” in.

It returns 0 if there’s nothing there, otherwise it compares the length of the multiple options string with the length of the same string without the commas.

[Thomas_McLeod]

This works surprisingly well. Feels like crazy over engineering for what should just work with the COUNTA or COUNTALL functions.

[Philip_Johnson]

Very clever - thanks for this hack!