Has anyone built a calculated field using IFIN formula for a multiselect field on a Custom Field?

[JaimeDa]

I'm trying the IFIN(value, value1, value2,..., trueExpression, falseExpression). However, my multi select field has numerous instances that need to be combined. When I reached out to support they said "CONTAINS" wouldn't work for what I'm trying to do. Has anyone had to do this or know enough about calculated fields? I'm trying to create a report to sum the projects for each True Expression but am not able to get the data.

[ChloeRo]

We have something similar, but it uses SUM and IF. I would imagine you could swap "IF" for "IFIN" though. Essentially, we set the true statement to the value "1" and the false statement to "0". That way, if true is met, it counts for 1 point, if false is met, it counts for 0 points. Then the SUM function that surrounds the formula will add up all the points. It's sort of confusing to explain, but I'll post a generalized example:

=SUM(IFIN(Multi-Select Field,valueX,1,0),IFIN(Multi-Select Field,valueY,1,0),IFIN(Multi-Select Field,valueZ,1,0))

So, in theory, if we had something where all 3 IFIN statements were met, WF would return the value 3. If only 2 IFIN statements were met, WF would return the value 2. Depending on how many multi-select options you have, this could get cumbersome. Some of our multi-selects have about 100 options and that wouldn't be practical for this use. In those cases, you probably won't get much use out of this calculated field.

Is that sort of what you needed?

[ChloeRo]

I just tested this out with CONTAINS and it seems to work:

SUM(IF(CONTAINS(Multi-Select Field,valueX),1,0),IF(CONTAINS(Multi-Select Field,valueY),1,0),IF(CONTAINS(Multi-Select Field,valueZ),1,0))

[JaimeDa]

Wow thank you! I'll try it in my form. I appreciate it so much.

[AlexMu]

Hi @Jaime Davidson‚ , did this end up working for you? I've tried it in my instance, but when multiple items are selected, it doesn't seem to be summing anything up, only when a single option is selected. Thanks!

EDIT: Figured it out, it looks like the variables should be flipped. This calculates correctly when multiple items are selected:

SUM(IF(CONTAINS("Text to find in quotes",MultiselectField),1,0),IF(CONTAINS("Text to find in quotes",MultiselectField),1,0),IF(CONTAINS("Text to find in quotes",MultiselectField),1,0))

[JaimeDa]

I ended up working with a consultant who helped me with the formula. We needed to figure out IF and Contains because we had multiple options for the field where someone can select multiple choice or only one.

IF statement looked like this:

IF(CONTAINS("x",{multiselect field})&&CONTAINS("y",{multiselect field})||CONTAINS("x",{multiselect field})&&CONTAINS("z",{multiselect field}),"Multiple Parent Regions")

CONTAINS statement looks like this

IF(CONTAINS("x",{multiselect field}}),"x",IF(CONTAINS("y",{multiselect field}}),"y",IF(CONTAINS("z",{multiselect field}}),"z")))

In my formula, these two statements were strung together.

[Femke_Crijns]

This statement was so helpful to me. I struggled with my formula for weeks; this CONTAINS contribution gave me new insights and inspiration to sum values of a multi-select field.

So, thanks for sharing!