I have a result which contains multiple text from other tables.

So it can contain yes, yes, no or any other variation.

Based on this table I need a new (if?) formula to give me a new value if there are 3x yes.

Who can help me with this?

I have a result which contains multiple text from other tables.

So it can contain yes, yes, no or any other variation.

Based on this table I need a new (if?) formula to give me a new value if there are 3x yes.

Who can help me with this?

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.