FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT()

AppSheet Tips & Tricks
,
1

Core concepts & functions

Adding & updating rows

Excel alternatives

Last row of the spreadsheet

Rows of this thing only

Miscellaneous

44 Likes
2

How do I get a column value from the last row of the spreadsheet?

LOOKUP(
  MAX(My Table[_ROWNUMBER]),
  "My Table",
  "_ROWNUMBER",
  "Wanted Column"
)

Replace My Table with the name of the table from which you want the column value; and Wanted Column with the name of the column whose value you want.

See also: MAX()

22 Likes
3

How do I get the last row of the spreadsheet?

MAXROW(
  "My Table",
  "_ROWNUMBER"
)

Replace My Table with the name of the table whose row you want.

7 Likes
4

How do I get a column value from the last row of this thing only?

LOOKUP(
  MAX(
    SELECT(
      My Table[_ROWNUMBER],
      ([_THISROW].[Thing] = [Thing])
    )
  ),
  "My Table",
  "_ROWNUMBER",
  "Wanted Column"
)

Replace My Table with the name of the table from which you want the column value; Thing with the name of the column containing a value that identifies the thing you want (e.g., Order ID); and Wanted Column with the name of the column whose value you want.

See also: MAX()

20 Likes
5

How do I get a list of rows of this thing only?

FILTER(
  "My Table",
  ([_THISROW].[Thing] = [Thing])
)

Replace My Table with the name of the table whose row you want; and Thing with the name of the column containing a value that identifies the thing you want (e.g., Order ID).

8 Likes
6

How do I get a list of column values from rows of this thing only?

To include duplicate values in the result:

SELECT(
  My Table[Wanted Column],
  ([_THISROW].[Thing] = [Thing])
)

Replace My Table with the name of the table whose row you want; Wanted Column with the name of the column whose value you want; and Thing with the name of the column containing a value that identifies the thing you want (e.g., Order ID).

To omit duplicates so that each value occurs only once in the result:

SELECT(
  My Table[Wanted Column],
  ([_THISROW].[Thing] = [Thing]),
  TRUE
)
9 Likes
7

How do I do SUMIF() or SUMIFS()?

SUM(
  SELECT(
    Orders[Total Amount],
    AND(
      ([Customer] = [_THISROW].[Customer]),
      ([Order Date] >= [_THISROW].[Order Date])
    )
  )
)

Use SELECT() to gather values from a single column only.

See also: AND(), SUM()

9 Likes
8

How do I do COUNTIF() or COUNTIFS()?

COUNTIF(col, expr), where col is a whole-column reference (e.g., A), is similar to:

COUNT(
  FILTER(
    "table",
    ([col] expr)
  )
)

COUNTIFS(col1, expr1, col2, expr2, ...), where col1 and col2 are each whole-column references, is similar to:

COUNT(
  FILTER(
    "table",
    AND(
      ([col1] expr1),
      ([col2] expr2),
      ...
    )
  )
)

To limit the rows included in the COUNT() range (e.g., as with COUNTIF(A3:A7, ...)), include the limit criteria in the FILTER() sub-expression, similar to this:

COUNT(
  FILTER(
    "table",
    AND(
      ([_ROWNUMBER] >= low-row-num),
      ([_ROWNUMBER] <= hi-row-num),
      ([col] expr)
    )
  )
)

To count across multiple columns (e.g., as with COUNTIF(A:C, ...)), you may add the results of an additional FILTER() sub-expression for each additional column:

COUNT(
  FILTER(...)
  + FILTER(...),
  ...
)

See also: AND(), COUNT()

9 Likes
9

Your a Beast

2 Likes
10

How are FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() related?

FILTER(), LOOKUP(), MAXROW(), and MINROW() are all built upon SELECT(), so they all share similar capabilities and limitations.

  • FILTER("table", select-expression) is equivalent to:

    SELECT(
  table[key-column],
  (select-expression)
)

  • LOOKUP(match-expression, "table", "match-column", "result-column") is equivalent to:

    ANY(
  SELECT(
    table[result-column],
    ([match-column] = (match-expression))
  )
)

    Note that if multiple rows match, the result value from only one row, chosen at random, will be returned.

  • MAXROW("table", "column", select-expression) is equivalent to:

    ANY(
  SELECT(
    table[key-column],
    AND(
      (
        [column] = MAX(
          SELECT(
            table[column],
            (select-expression)
          )
        )
      ),
      (select-expression)
    )
  )
)

    Note that if multiple rows contain the maximum value, only one row, chosen at random, will be returned.

  • MINROW("table", "column", select-expression) is equivalent to:

    ANY(
  SELECT(
    table[key-column],
    AND(
      (
        [column] = MIN(
          SELECT(
            table[column],
            (select-expression)
          )
        )
      ),
      (select-expression)
    )
  )
)

    Note that if multiple rows contain the minimum value, only one row, chosen at random, will be returned.

  • REF_ROWS("table", "ref-column") is equivalent to:

    SELECT(
  table[key-column],
  ([ref-column] = [_THISROW])
)
17 Likes
11

@Steve, This is excellent. Thanks for doing all this.

2 Likes
12

How do I get the last row of this thing only?

MAXROW(
  "My Table",
  "_ROWNUMBER",
  ([_THISROW].[Thing] = [Thing])
)

Replace My Table with the name of the table whose row you want; and Thing with the name of the column containing a value that identifies the thing you want (e.g., Order ID).

6 Likes
13

Bookmarked and appreciated

1 Like
14

All very useful references @Steve. Thank you.

1 Like
15

Is this a new row?/Does this row already exist?

Is this a new row?

NOT(IN([_ROWNUMBER], My Table[_ROWNUMBER]))

or:

ISBLANK(
  FILTER(
    "My Table",
    ([_ROWNUMBER] = [_THISROW].[_ROWNUMBER])
  )
)

Does this row already exist?

IN([_ROWNUMBER], My Table[_ROWNUMBER])

or:

ISNOTBLANK(
  FILTER(
    "My Table",
    ([_ROWNUMBER] = [_THISROW].[_ROWNUMBER])
  )
)

In the above, replace My Table with the name of the table about which you’re inquiring.

When modifying rows, such as in forms or with actions, expressions act on a temporary copy of the row. Only after the form is saved or action completed is the new row data saved to the table. Until saved, the data in the table remains as it was when the form was opened or the action started. Expressions that explicitly reference tables, such as table-column references and SELECT() and its derivatives, can be used to access the table directly, bypassing any pending changes in the temporary copy of the row.

See also: FILTER(), IN(), ISBLANK(), ISNOTBLANK(), List from Table-Column Reference, NOT()

11 Likes
16

What is the previous value of this column?

The simplest way is to dereference [_THISROW_BEFORE]:

[_THISROW_BEFORE].[Wanted Column]

Replace Wanted Column with the name of the column whose value you want.

LOOKUP() could also be used:

LOOKUP(
  [_THISROW].[_ROWNUMBER],
  "My Table",
  "_ROWNUMBER",
  "Wanted Column"
)

Replace My Table with the name of the table from which you want the column value; and Wanted Column with the name of the column whose value you want.

When modifying rows, such as in forms or with actions, expressions act on a temporary copy of the row. Only after the form is saved or action completed is the new row data saved to the table. Until saved, the data in the table remains as it was when the form was opened or the action started. Expressions that explicitly reference tables, such as table-column references and SELECT() and its derivatives, can be used to access the table directly, bypassing any pending changes in the temporary copy of the row.

See also: List from Table-Column Reference, LOOKUP()

9 Likes
17

You can also use the THISROWBEFORE /AFTER functions… They’re supposed to be for reports and such, but they seem to work fine in other expressions.

1 Like
18

Uhh… thats news to me

4 Likes
19

Do you have an example where you used it successfully in other expressions?

2 Likes
20

@Bahbus and @Jonathon Here is an example in which I have an app formula looking toward a status column to pin an actual start date…

IFS(
OR(
	AND(
  [_THISROW_BEFORE].[status_code]<>"Not Started",
  [status_code]<>"Not Started"
  ),
  AND(
  [_THISROW_BEFORE].[status_code]="Not Started",
  [status_code]="Not Started"
  )
  ),
[_THISROW_BEFORE].[act_start_date],
AND(
[_THISROW_BEFORE].[status_code]="Not Started",
[status_code]<>"Not Started"
),
NOW()-
((TIME(
CONCATENATE(
  MOD(FLOOR([target_drtn_hr_cnt]), 24),
  ":",
  (MOD(((60 * 60) * [target_drtn_hr_cnt]), (60 * 60)) / 60),
  ":",
  MOD(((60 * 60) * [target_drtn_hr_cnt]), 60)
	)
)
- "00:00:00"
	) + (FLOOR([target_drtn_hr_cnt] / 24) * 24))
)
5 Likes