I have this pretty straightforward formula in my

At lease if the filter is allowed to be set like this in column 2, this could work.

CodePudding user response：

You could potentially reconfigure your formulas such that rather than spilling the SUM into row 2 using an array literal as you are currently doing, instead you include the SUM into row 1 but place the sum below the Header using a carriage return character:

={"Number of purchases"&CHAR(10)&SUM(C2:C);...}

N.B. 1 - as row 2 would no longer be required for the SUM you can move all of your formulas up one row to run from row 2 instead, so the SUM would be calculated from C2:C instead in this case, as I have shown.

N.B. 2 - If you want to spill results out of the header cell of a column as you have been doing, AND have some sort of aggregation of the results in THAT column also in the header, you can't just SUM the range of cells below the header as due to order-of-operations the SUM gets generated before the other cells get filled in (so will always = 0...) - you need to calculate the sum in-line using the same array expression as used to spill the cells, i.e.:

={"Header"&CHAR(10)&SUM(array_expression);array_expression}

For a long/complex array_expression, consider using a LAMBDA so you don't have to explicitly repeat the whole thing.