We are on prem 2013, and I have 1 lookup list with three separate columns looking up to that one list. I also have three separate single line of text fields to allow users to choose at up to six Vendor options. We want users to pick at least two - - any two. I need a formula that would require two out of the six fields. See image. Thanks!
I came up with this solution.
For each lookup and single line of text control, add a calculated value with a formula as such:
This will populate the field with either 0 (if empty) or 1.
Add another calculated value which sums all the 0 and 1s up.
To the sum field, add a validation rule so that it is invalid if its value is below 2.
Just discovered that you can sum up without using all the IFs, making the formula for the sum field like this:
!isNullOrEmpty(prefVen1) + !isNullOrEmpty(prefVen2) + !isNullOrEmpty(prefVen3) //and so on
You could also take above formula and put it in a validation rule directly
This way you don´t need to add all calculated values for each control
This was a little tricky but I found a solution.
The only way I know how to count controls that are not empty is to use the count() but this only applies to Repeating sections.
So I created 2 repeating sections. One for the lookup and one for the single lines of text and set them to default to 3 rows and made them so that rows could not be added or deleted.
I then added a Calculated value control that summed up the populated controls.
Then set a validation rule on the calculated control to say that the sum could not be less than 2.
I attached a sample form for you to test. I used a Choice control instead of a lookup for testing and POC but it should still work with a lookup control.
This has less controls and calculations than the above solution so will be more efficient.