Using FIND() on top of a ROLLUP field returns inconsistent results

[Marine_Boudeau]

Hi,

I’m using a lookup field which aggregates a list of single selects (containing values like : “Yes”, “No”).
Within the lookup fields, I’m using the formula : ARRAYJOIN(values)

It works great. Then I have a formula field with a FIND() :
FIND("Yes", {my lookup colomn})

The result I get with this formula is way below the amount I am supposed to get. About 3 times lower.

Even stranger, if I change the lookup formula to ARRAYJOIN(values, ', '), I get yet a different number.

What gives?
Thanks for your help!

[Justin_Barrett · Accepted solution]

Welcome to the community, @Marine_Boudeau! :grinning_face_with_big_eyes: I think you might be misunderstanding the purpose of the FIND() function. FIND() looks at a string and returns the position of the first occurrence of the item you’re seeking. However, it sounds like you’re trying to count the number of “Yes” replies, which is why you’re getting unexpected results. (On a side note, it sounds like you’re using a rollup field, not a lookup. Rollup fields have aggregation formulas, whereas lookup fields don’t.)

To get what you want will require a different approach. First, change your rollup aggregation formula to this (no space between the quotes):

ARRAYJOIN(values, "")

This will create a string something like “YesNoNoYesNoNoNoYes…”, with all responses mashed together.

In the formula field that processes this string, you want to strip out all “No” results and count the remaining “Yes” results. This can be done a couple of ways, but they both start the same way.

Begin your formula with a SUBSTITUTE() that replaces each “No” with an empty string:

SUBSTITUTE({Your Rollup Field Here}, "No", "")

There are two ways you can take that output and count the remaining “Yes” bits. One is to take the length and divide it by 3 (the length of the string “Yes”). With that, the full formula is this:

LEN(SUBSTITUTE({Your Rollup Field Here}, "No", "")) / 3

A slightly lengthier option is to wrap that first SUBSTITUTE() inside a second SUBSTITUTE() to replace each “Yes” with a single character, then count the length of that resulting string:

LEN(SUBSTITUTE(SUBSTITUTE({Your Rollup Field Here}, "No", ""), "Yes", "X"))

A more advanced option is to do all of this processing inside the rollup formula itself, avoiding the need for another formula field. Using the shorter of the two versions above, your rollup field aggregation formula would turn into this:

LEN(SUBSTITUTE(ARRAYJOIN(values, ""), "No", "")) / 3

That will make the rollup directly return the number of “Yes” results.

[Marine_Boudeau]

Hi Justin. Thanks so much for this and your quick reply. I fixed the topic of my post, yes, I had meant Rollup instead of Lookup.

What I don’t understand is why I have to go substract the other words instead if just counting the values I am interested in. The reason I ask if because I don’t just have “Yes” and “No” but I also have “Partial” and “N/a”.

Would you recommend the same solution?

Thank you!
Marine

[Marine_Boudeau]

I got it, I did that and it works perfectly. Thank you! Though interested if there’s a better way to do it!

LEN(
  SUBSTITUTE(
    SUBSTITUTE(
      SUBSTITUTE(
        SUBSTITUTE(
          SUBSTITUTE(
            SUBSTITUTE(
              {Support accessible},
              "No", ""
            ),
            "Partial", ""
          ),
          " ", ""
        ),
        "n/a", ""
      ),
      "–", ""
    ),
    "Yes", "X"
  )
)

[Justin_Barrett]

That’s pretty much the best option for now. Airtable formulas don’t currently have a function that counts the number of occurrences of one string inside a larger string.