Expand my Community achievements bar.

I have a checkbox field in a custom form for different creative asset types. I want to create a calculated field that totals the number of different asset types (number of checkboxes checked) for that field. Is this possible?

Avatar

Level 4

For example if they check 4 options, the total would be 4.

2 Replies

Avatar

Level 10

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

This is a great tip! This is applicable is so many ways! Love it!

Simple fix to the "gotcha":

IF(ISBLANK({Checkbox Field}),0,SUM(1,SUB(LEN(REPLACE({Checkbox Field},",","**")),LEN({Checkbox Field}))))