How to use a list or array as filter condition?

1

Is it possible to use an array or list (that is within a single cell) as a condition in a filter formula?

Here is my example:

coda%20gif
coda gif.gif1256×361 218 KB

In Tabale2.Column1 in each row I have different arrays/lists of items selected manually from Table1.Column1
For Table2.Column2 I’m looking for a column formula that filters Table1 based on the selection and SUM the filtered Table1.Column2 values.

I’ve tried the CONTAINS formula but it doesn’t seem to work this way:
=[Table 1].filter(Contains([Table 2].[Column 1],[Column 1])).[Column 2].sum()

Did I just miss something or there’s another totally different way to achieve this result?
Thanks a lot!

1 Like
2

Zsolt,

The IN formula can be of help here. https://coda.io/formulas#In

So, in this case, the formula for [Table 2].[Column 2] would be:

[Table 1].FILTER(IN([Column 1], thisRow.[Column 1].[Column 1])).[Column 2].Sum()

A couple of things to note:

  1. The first argument of the IN formula corresponds to [Table 1].[Column 1]
  2. thisRow in the second argument corresponds to the row in [Table 2], since the formula is on a column in [Table 2]
  3. thisRow.[Column 1].[Column 1] --> the second [Column 1] exists because thisRow.[Column 1] is a list of @ref to rows in [Table 1]. You are therefore de-referencing it to pull out the value of [Column 1] from that @ref.

Does that help?

Thanks,
Hari.

5 Likes
3

Hari,

thanks a lot for the formula and the detailed explanation!

The de-referencing you mentioned at point 3. didn’t work for me in my actual doc:

For the example tables [Table 1] and [Table 2] it worked without the additional .[Column 1] de-referencing. And for my actual doc I had to include the de-referencing parameter .[Column 1] to the Select List’s Selectable Items function to de-reference it and this way it works perfectly for me.

Thank you very much again for the help!
Zsolt

4

@Zsolt_Bako, humm that’s interesting.

I tried to recreate your schema in this document. https://coda.io/d/Filter-with-select-list_dZ_3iTU5qYG/Section-1_suSN7#_luplf

(If you request access, I’ll approve. Or, if you can DM me your email, I’ll share the document with that email).

How does the schema map in the document that I created map to yours?

Thanks,
Hari.

5

Thanks, here is my example doc: https://coda.io/d/Example_dvokXqvSI3g/Section-1_suQkM#_luXI9

6

I think the difference is the column format of [Column 1] in [Table 2]

  • Lookup table for you
  • Select list for me

And in the select list’s selectable items formula I already de-referenced it: [Table 1].[Column 1]

Could it be the cause why the in-forumla de-referencing did work for me?

Thanks,
Zsolt

7

Indeed, that’s the source of the difference. In your select list definition for [Table 2].[Column 1], the values in the select list are the text values in [Table 1].[Column 1]'s cells across rows.

On the other hand, when using a LOOKUP, the values in the select list are references to rows in [Table 1], and not values in cells in [Table 1].[Column 1] itself. Since they are references to rows, you use the additional dereference.

Thanks,
Hari.