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

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()

3 Likes

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()

3 Likes

Your a Beast :100:

1 Like

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])
    )
    
5 Likes

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

1 Like

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

2 Likes

Bookmarked and appreciated

1 Like

All very useful references @Steve. Thank you.

1 Like

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

Is this a new row?

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

Does this row already exist?

ISNOTBLANK(
  FILTER(
    "My Table",
    ([_THISROW].[_ROWNUMBER] = [_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. 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(), ISBLANK(), ISNOTBLANK()

5 Likes

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. SELECT() and its derivatives can be used to access the table directly, bypassing any pending changes in the temporary copy of the row.

6 Likes

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.

Uhh… thats news to me :hushed:

2 Likes

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

2 Likes

@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))
)
4 Likes
1 Like

I might as well post the finish date time expression as well…
IFS(
AND(
[_THISROW_BEFORE].[status_code]<>“Completed”,
[status_code]=“Completed”
),
NOW(),
AND(
[_THISROW_BEFORE].[status_code]=“Completed”,
[status_code]<>“Completed”
),
“”,
AND(
[_THISROW_BEFORE].[status_code]=“Completed”,
[status_code]=“Completed”
),
[_THISROW_BEFORE].[act_end_date]
)

3 Likes

Hmm, I could have sworn that I remember @Aleksi or one of the other devs stating this wasn’t possible… But maybe they’ve been working on it. Or maybe it’s always been theoretically possible but has some sort of other limitation which is why they don’t emphasis it.

1 Like

[_THISROW_BEFORE] and [_THISROW_AFTER] works only when you actually SAVE the form and therefore both mainly works very well with the workflow triggering. Provided you want to compare a column’s value on a live form, then @Steve’s LOOKUP expression is the only way to achieve the desired target.

2 Likes