Counting multiple selects in formula


#1

Hi!

I’m new to formulas, and I can’t figure that one out, even though I’m sure it’s pretty simple.
I’ve got a table with a multiple select field, and I’d like to know at a glance how many of these multiple selects were selected in each row, so I’m making a formula field COUNTA ({name of multiple select field}) but all it does is tell me 1 if there’s something in there, and 0 if there’s nothing. It doesn’t count how many items I’ve selected. Can anybody help me find the right formula? Many thanks in advance!


#2

The COUNTA() function seems to only work in Rollup fields, which seem to be the only fields that treat arrays as arrays proper, rather than strings.

Your Multiple Select field is outputting a comma separated string of the values in it when passed to a formula field. Example:

A multi-select field
image

The multi-select field as output to a formula field
image
image

Since that is a string with commas between each value, we can use string functions to “count commas” to figure out how many options were selected in the Multiple Select field. We can do that by finding the length of the string, and then removing all the commas from the string and finding it’s length without commas, finding the difference between the two lengths (ie, how many commas there were), and then adding 1 to the result, since there is always 1 less comma than there are values in the list:

image

The formula:

IF(
   Publications,
   LEN(Publications) - 
   LEN(
      SUBSTITUTE(
         Publications,
         ",",
         ""
      )
   ) + 1
)

#3

Thank you, that is very clear and helpful! It’s crazy that it’s so complicated to make that one field happen, so I’m not surprised I couldn’t, hahaha - I am very grateful for your help figuring it out! Cheers! (oops sorry I am logged in from my other account apparently…)


#4

To be clear, you don’t need the field that I called “Publications Formula” above. All you need is the one I called “Options Selected”.

The one called “Publications Formula” I put there just to show you what a Formula field “sees” when you tell it to look at a Multiple Select field. You can see, from the fact that I’m asking it to reproduce my “Publications” field, that it reproduces it by putting commas in between each option if there is more than one.