This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: Using FIND() on top of a ROLLUP field returns ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Solved

Jump to Solution

0
989
2

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 09, 2020 10:09 AM

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!

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 09, 2020 11:18 AM

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.

Reply

4 Replies 4

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 09, 2020 11:18 AM

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 09, 2020 12:09 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 09, 2020 01:01 PM

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"
)
)
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 09, 2020 02:13 PM