Dcycle Blog

In a spreadsheet, do not reference a cell calculated using UNIQUE() or FILTER()

January 27, 2022

If you are using Google Sheet’s =UNIQUE() or =FILTER() functions (or both) to generate values in cells, you should never reference those cells directly.


If you have these cells in sh1:

  A B C
1 Bikes TRUE 1
2 Bikes TRUE 2
3 Skates FALSE 3
4 Boards FALSE 4
5 Skates TRUE 5

And you put the following formula in sh2’s cell A1:


You will get:

1 Bikes
2 Skates
3 Boards

With this formula:

=FILTER(sh1!A1:A, sh1!B1:B)

You will get:

1 Bikes
2 Bikes
3 Skates

Let’s imagine we want to calculate the sum of unique filtered items in sh1, we’d combine UNIQUE(), FILTER(), and SUMIF() in sh2, like this:

  A B
1 =unique(FILTER(sh1!A1:A, sh1!B1:B)) =SUMIF(sh1!A$1:A, A1, sh1!C$1:C)
2   =SUMIF(sh1!A$1:A, A2, sh1!C$1:C)

This yeilds the following results in sh2:

  A B
1 Bikes 3
2 Skates 8

(This calculates the sum in column C of sh1 of all lines which, at column A, are “Bikes” (1+2) and “Skates” (3+5) whether column B is TRUE or not. Line 4 (“Boards”) is ommitted because “Boards” has no lines where column B are TRUE.)

What not to do: Referencing a cell in sh2

Now let’s say you want to reference the value of “Skates” (8), you might be tempted to use the following basic formula:


This is risky, because any time the underlying data in sh1 changes, the data in sh2 will change as well. Let’s change the data in sh1 to the following:

  A B C
1 Bikes FALSE 1
2 Bikes FALSE 2
3 Skates TRUE 3
4 Boards TRUE 4
5 Skates FALSE 5

This causes sh2 to become:

  A B
1 Skates 8
2 Boards 4

Now, sh2!B2 refers no longer the value of “Skates” but to the value of “Boards”.

This might affect the logic of your spreadsheet.

An alternate approach: VLOOKUP

Instead of


If we know we want to reference the value of “Skates”, the following formula is more logical (albeit a bit more complex):


This will look up the value “Skates” in the first column (VLOOKUP always looks up a value in the first column, you can’t change that), then finds the row (in our first example it was row 2, and in our last example it is row 1), and finds the associated value in the second column (in this case column B).

The last argument to VLOOKUP is important: if the lookup values are fuzzy (such as dates), you will use TRUE here; if the values are non-fuzzy (Skates, Bikes are Boards), you will use FALSE here.

Now, whether “Skates” is in row 1 or row 2, the result will always be 8.

A much more robust spreadsheet. Just don’t corner people at parties with this information!