Google Sheets - Stopping ARRAYFORMULA messing up columns while sorting results with a total included

Question

I have this pretty straightforward formula in my Spreadsheet:

={"Number of purchases"; SUM(C3:C) ; ARRAYFORMULA(if(isblank(A3:A),,sumif('Raw Data'!A:A,A3:A,'Raw Data'!B:B)+sumif('Raw Data'!A:A,B3:B,'Raw Data'!B:B)))}

The issue is when I apply a filter to row 1, my SUM will be messed up if I sort the data. I would like my filter to remain in row 1 and not to row 2.

Any solution/help would be appreciated.

Answer 1

The best I can do is remove Column C, and stop the formula from breaking when you sort Column B.

Can't think of any way that could stop row2 being sort if you must use the build-in filter of google sheet in row1.

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