I’ll answer this based on a few assumptions; if I’ve assumed incorrectly, let me know and I’ll adjust accordingly.
When you say you
I assume you’re talking about a lookup or rollup field aggregating data from select, multi-select, formula, or other such fields in other tables, rather than a free-form text field (or aggregation from multiple free-form text fields). The distinction I’m trying to make is that it, expressed as a string[1], would literally read something like
yes,yes,no,maybe,yes
and not
YESyes, maybe, possibly, Y, OK, no, nope, no!no!no!
Assuming consistency of the data, then, you can test for an inclusion of at least three 'yes’s using this formula:
IF(
.. (LEN({Result}&",") - LEN(SUBSTITUTE({Result}&",","yes,",""))/4 >= 3,
..... [3YesValue],
..... [OtherValue]
.. )
(Please ignore the leading '.'s; they are there only to maintain indentation.)
What this formula does is to take the length of the {Result} string (with a comma appended, to match a ‘yes’ as the final element), subtract from it the length of {Result}&"," with every instance of ‘yes,’ removed (that is, replaced by the empty string, “”), and divide the difference by 4. If that result is 3 or greater, it returns [3YesValue] – however you wish to flag the existence of 3 or more 'yes’s – or otherwise returns [OtherValue] indicating 2 or fewer 'yes’s were present.
If I’m wrong about the nature of your data, or if I’ve misinterpreted your need, let me know, and I’ll see if I can think of another approach.
. -------------
- Either explicitly through a call to
ARRAYJOIN() or via Airtable’s implicit casting of an array to a string prior to most processing.
