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!

[Rasha]

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

[dk82734]

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

[VictoriaPlummer]

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}, ",", "")))

[Kyle_Langenbach]

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!

[Justin_Barrett]

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