Counting multiple selects in formula

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
)

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

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.

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

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?

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?

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

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.

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.

LEN({Pickup ZIP}&'')

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

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

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

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

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

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

One small modification to avoid counting NO zip as 1 Zip

IF((
(
  LEN({ZIP Arrayjoin}) - 
  LEN(
    SUBSTITUTE(
      {ZIP Arrayjoin},
      ',',
      ''
    )
  )
) + 1
) > 1, 
((
  LEN({ZIP Arrayjoin}) - 
  LEN(
    SUBSTITUTE(
      {ZIP Arrayjoin},
      ',',
      ''
    )
  )
) + 1), 
"0")

IF((
(
  LEN({ZIP Arrayjoin}) - 
  LEN(
    SUBSTITUTE(
      {ZIP Arrayjoin},
      ',',
      ''
    )
  )
) + 1
) > 1, 
((
  LEN({ZIP Arrayjoin}) - 
  LEN(
    SUBSTITUTE(
      {ZIP Arrayjoin},
      ',',
      ''
    )
  )
) + 1), 
"0")

That solution doesn’t quite work – it incorrectly treats arrays of size one as zero.

The solution is hidden in this article, but SUBSTITUTE() will get you what you need. Change {Collaborator} for your needs.

IF( LEN({Collaborator}) = 0, 0, LEN(CONCATENATE(",", {Collaborator})) - LEN(SUBSTITUTE({Collaborator}, ",", "")))

This is a bit tangential to the OP but haven’t been able to find a solution.

I have 10 single select columns (yes or no) in a table, and for each row I need to sum (count) the number of selected yes choices. It’s not a rollup, nor is it a multi select where I need to use the LEN for commas, etc. Can someone help? Thanks!

Welcome to the community, @Kyle_Langenbach! :grinning_face_with_big_eyes: Here are the steps involved, all of which can be done in a single field:

• Concatenate all fields into a single string. That will leave you with something like “YesNoNoYesYesYesNoYesNoNo”
• Use the SUBSTITUTE() function to change all instances of “No” in that string mashup into empty strings. That will leave you with “YesYesYesYesYes”
• Use another SUBSTITUTE() function to change all instances of “Yes” into a single character (I’ll use “X”). That will leave you with “XXXXX”
• Use LEN() to count the length of the remaining string, and that will tell you how many times “Yes” appears. In this example, we’re left with 5.

Here’s my quickly hacked together version of that formula:

LEN(
    SUBSTITUTE(
        SUBSTITUTE(
            {Field 1} & {Field 2} & {Field 3} & {Field 4} & {Field 5} &
            {Field 6} & {Field 7} & {Field 8} & {Field 9} & {Field 10}
            , "No", ""
        ), "Yes", "X"
    )
)