Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

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

Avatar

Level 2

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.

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

6 Replies

Avatar

Level 4

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?

Avatar

Level 4

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

Avatar

Level 2

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

Avatar

Level 3

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

Avatar

Level 2

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.

Avatar

Level 3

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!