If counting text values


#1

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?


#2

What are you trying to do? I think it could be possible with another approach.


#3

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

  1. Either explicitly through a call to ARRAYJOIN() or via Airtable’s implicit casting of an array to a string prior to most processing.