CODEWITHSUNDEEP
google-sheets
Rob
Rob

Reputation: 7099

Speed up filter and sum results based on multiple criteria?

I am filtering then summing transaction data based on a date range and if a column contains one of multiple possible values.

example data

    A      |  B  |                C                                  |   D
-----------|-----|---------------------------------------------------|-------
11/12/2017 | POS | 6443 09DEC17 C , ALDI 84 773 , OFFERTON GB        | -3.87
18/12/2017 | POS | 6443 16DEC17 C , CO-OP GROUP 108144, STOCKPORT GB | -6.24
02/01/2018 | POS | 6443 01JAN18 , AXA INSURANCE , 0330 024 1229 GB   | -220.10

I'm currently have the following formula, that works but is really quite slow.

=sum(
  iferror(
     filter(
         Transactions!$D:$D, 
         Transactions!$A:$A>=date(A2,B2,1),
         Transactions!$A:$A<=date(A2,B2,31),
         regexmatch(Transactions!$C:$C, "ALDI|LIDL|CO-OP GROUP 108144|SPAR|SAINSBURYS S|SAINSBURY'S S|TESCO STORES|MORRISON|MARKS AND SPENCER , HAZEL GROVE|HAZELDINES|ASDA")
     )
     ,0
  )
) * -1

The formula is on a seperate sheet that is just a simple view of the results breakdown for each month of a year

  |   A  | B  |     C
--|------|----|----------
1 | 2017 | 12 | <formula>  # December 2017
2 | 2017 | 11 | <formula>  # November 2017
3 | 2017 | 10 | <formula>  # October 2017

Is there a way to achieve this that would be more performant?

I tried using ArrayFormula and SUMIF which works for the string criteria but to add more criteria with SUMIFS for the date, it stops working.

I couldn't figure out a way to utilize INDEX and/or MATCH

Upvotes: 1

Views: 411

Answers (1)

Max Makhrov
Max Makhrov

Reputation: 18717

=query(filter( {Transactions!$A:$A,
         Transactions!$D:$D},
         regexmatch(Transactions!$C:$C, "ALDI|LIDL|CO-OP GROUP 108144|SPAR|SAINSBURYS S|SAINSBURY'S S|TESCO STORES|MORRISON|MARKS AND SPENCER , HAZEL GROVE|HAZELDINES|ASDA")
     ), "select year(Col1), month(Col1)+1, -1*sum(Col2) group by year(Col1), month(Col1)+1", 0)

The result is a table like this:

year()  sum(month()1())    sum 
2017                 11    3.87
2017                 12    6.24

Add labels if needed. Sample query text with labels:

"select year(Col1), month(Col1)+1, -1*sum(Col2) group by year(Col1), month(Col1)+1 label year(Col1) 'Year', month(Col1)+1 'Month'"

The result:

Year   Month    sum 
2017      11    3.87
2017      12    6.24

Explanations

  • the single formula report reduces the number of filter functions, so must work faster.
  • Used query syntax. more info here.

Upvotes: 1

Related Questions