Skip to main content
Solved

COUNTALL(Multiple Options) always returns 1

  • November 20, 2015
  • 4 replies
  • 61 views
S
Forum|alt.badge.img+4

Expected behaviour: return the number of selected items.

Best answer by Simon_Brown

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.

C
T

4 replies

A
Forum|alt.badge.img+3
  • Alexander_Sorok
  • Participating Frequently
  • December 4, 2015

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.

S
I
S
  • Simon_Brown
  • New Participant
  • Answer
  • July 17, 2016

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.

C
A
T
T
  • Thomas_McLeod
  • New Participant
  • December 30, 2016

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.


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

C
P
P
  • Philip_Johnson
  • New Participant
  • November 30, 2018

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.


Very clever - thanks for this hack!

A
Terms & ConditionsAccessibility statement