Dec 13, 2018 12:52 AM
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!
Dec 13, 2018 09:09 AM
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
)
Dec 13, 2018 11:55 AM
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…)
Dec 13, 2018 11:59 AM
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.
May 22, 2019 05:46 PM
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?
May 22, 2019 07:57 PM
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?
May 22, 2019 09:36 PM
ah… got it! I see what I missed now. And yes, that’s precisely what I’m trying to do.
May 26, 2019 12:23 PM
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.
May 27, 2019 12:12 PM
Sorry about that, @Rasha.
There’s another little trick you can use here, called “counting commas”, that should work for your situation.
LEN({Pickup ZIP}&'')
The &''
is necessary to coerce the array in the rollup into a string
SUBSTITUTE({Pickup ZIP}&'', ',', '')
LEN(SUBSTITUTE({Pickup ZIP}&'', ',', ''))
LEN({Pickup ZIP}&'') - LEN(SUBSTITUTE({Pickup ZIP}&'', ',', ''))
(
LEN({Pickup ZIP}&'') -
LEN(
SUBSTITUTE(
{Pickup ZIP}&'',
',',
''
)
)
) + 1
Try that and see if it gets you what you want.
May 28, 2019 02:54 PM
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