Skip to main content

Filters in Spreadsheet

The filter argument, its operators, and taking a filter value from a cell with GI.WHERE

Written by Holly Conrad Smith

A filter narrows a GI.* formula down to the geometries you actually mean: one usage, one bed type, a floor count above a threshold. It appears as an argument in most of the functions you use to build a formula.

The filter argument

A filter is a piece of text, in quotes. That one fact explains every quote mark you will see. One clause reads field op value with no quotes around the value inside the text, and a comma joins clauses with AND:

  • "usage=Residential"

  • "usage=Retail, levels>3"

  • "name~tower"

The operators are = != > < >= <= ~, where ~ means "contains".

Taking a filter value from a cell

A filter is text, so a value living in a cell has to be joined onto it with &:

=GI.TOTAL("grossArea", "usage="&$A20)

GI.WHERE does the same job without the string-joining. It takes a field and a value in pairs:

=GI.TOTAL("grossArea", GI.WHERE("usage", $A20))

An operator glued to the field name is kept, so GI.WHERE("levels>", 3) means "levels greater than 3". Without one, it means equals. This is also what a stamped schedule writes for you, so you rarely need to type it by hand.

When you stamp a schedule, Spreadsheet writes GI.WHERE for you. You can also use filters inside GI.SUMEACH and its relatives.

Did this answer your question?