Counting multiple selects in formula

[Marie_Peze]

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!

[Jeremy_Oglesby]

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

The multi-select field as output to a formula field

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:

The formula:

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

[Expatica_Team]

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…)

[Jeremy_Oglesby]

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.

[Rasha]

Jeremy_Oglesby:

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.

Hi @Jeremy_Oglesby - I know this post is a bit old but I ran into a similar obstacle. Noticed you mention that COUNTA() works for Rollup fields - hasn’t been the case for me. Just results in a 1.

I tried the string function you shared but that lead to an error. Also, If I try to duplicate the rollup field, then convert it to a linked record in another table, then apply COUNTA()… that’s also an error. Have you come across anything that does work since December?

[Jeremy_Oglesby]

Hi @Rasha,

What I meant by “only works for Rollup fields” is that COUNTA(values) literally only works when written in the Rollup function section of an actual Rollup field. Formula fields have no knowledge of COUNTA().

Are you just trying to count the number of ZIP codes in the {Pickup ZIP} field?

[Rasha]

ah… got it! I see what I missed now. And yes, that’s precisely what I’m trying to do.

[Rasha]

So your suggested formula above which seems to have worked for the Publications multiple select field can’t work for my ZIPs rollup field. Just want to confirm because I did try a few variations.

[Jeremy_Oglesby]

Sorry about that, @Rasha.

There’s another little trick you can use here, called “counting commas”, that should work for your situation.

1. Get the total length, in characters, of the original string

LEN({Pickup ZIP}&'')

The &'' is necessary to coerce the array in the rollup into a string

2. Remove all the commas from the original string

SUBSTITUTE({Pickup ZIP}&'', ',', '')

3. Get the total length, in characters, of the string without commas

LEN(SUBSTITUTE({Pickup ZIP}&'', ',', ''))

4. Subtract the length of string without commas from length of original string

LEN({Pickup ZIP}&'') - LEN(SUBSTITUTE({Pickup ZIP}&'', ',', ''))

5. Add 1 to the result, since there is always 1 more value than there are commas in an array/list

(
  LEN({Pickup ZIP}&'') - 
  LEN(
    SUBSTITUTE(
      {Pickup ZIP}&'',
      ',',
      ''
    )
  )
) + 1

Try that and see if it gets you what you want.

[Rasha]

Hey @Jeremy_Oglesby - thank you for your help! weird, that didn’t work. But here’s what did. I used an ARRAYJOIN function first, then applied your formula in the # ZIPs field. I’m sure they can be combined.

(
  LEN({ZIP Arrayjoin}) - 
  LEN(
    SUBSTITUTE(
      {ZIP Arrayjoin}&'',
      ',',
      ''
    )
  )
) + 1