Adding a Total Row to a Table of Values

Some functions such as FILTER, QUERY, UNIQUE, etc., return a variable number of rows based on the data.

This tip explains how to add, for example, a total row following a variable-sized data table without leaving an empty row.

Adding a Total Row

In this example, the FILTER function returns a table with members who have not yet paid for their purchase:

google sheets filter add total

The objective here is to add a total row to the returned table.

Before reading the rest of the explanations, be sure to read the page on the use of braces if you are unfamiliar with their use.

Since the returned table here is on 3 columns, the added row will also need to be a value table on 3 columns.

The first value will be empty, the second "TOTAL", and the third will be the result of the SUMIF function:

={FILTER(A2:C15,D2:D15="");{"","TOTAL",SUMIF(D2:D15,"",C2:C15)}}

google sheets filter total row sum add

And to apply a different format to the total row, use a conditional format:

Even with changes in the size of the table returned by the FILTER function, the total row is correctly displayed:

google sheets filter total row braces add