Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-formula

How to place a row (subtotal) below a stream of populated data when its size is unknown?

The question is pretty straight forward: If I'm using a formula that will populate the column with a unknown range of data, no matter what formula it is (filter, query, arrayformula, unique), how would I make a row, or at least a cell, to be automatically placed right bellow the last cell of the populated data?

Solution

  • Here's what I would use:

    =LET(data,<your_formula>,
     {data;SUM(data)})

    For example:

    =LET(data,FILTER(A1:A5,B1:B5),
     {data;SUM(data)})