Search code examples
google-sheetsgoogle-sheets-formulaformulaspreadsheet

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

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.

Solution

  • 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.

    enter image description here

    enter image description here

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

    enter image description here