How to get running total with two conditions using Arrayformula in Google Sheet

Question

Can someone help me to build formula to get running total with two conditions brands and date using arrayformula? exp for G4 i want to get sum of QTY before 11th Nov 2022 (F4) for brand D, DG and DA (G2) and so on until column F is not null.

Thanks.

Answer 1

This formula uses QUERY to sum the QTY and group up the given data by brand and date, than uses BYROW with INDEX and FILTER to get the RUNNING TOTAL of the QUERY.

BRAND to lookup need to be entered in range 'G2' and separated with ','(without space - separator can be changed at the last line of code).

You can edit the range at the bottom line of the code to change the range of reference.

Formula in range 'F4':

=ArrayFormula(LAMBDA(DATARANGE,SELECTEDBRAND,SEPARATOR,
QUERY(SPLIT(
 LAMBDA(QUERY,
  LAMBDA(COL_DATE,COL_TOTAL,
   BYROW(QUERY,LAMBDA(ROW,
    LAMBDA(DATE,TOTAL,
     JOIN(";",DATE,SUM(FILTER(COL_TOTAL,COL_DATE<=DATE)))
    )(INDEX(ROW,,1),INDEX(ROW,,2))
   ))
  )(INDEX(QUERY,,1),INDEX(QUERY,,2))
 )(
  QUERY(
   QUERY({DATARANGE},"SELECT Col3,SUM(Col4) WHERE "
    &IF(NOT(ISNUMBER(FIND(SEPARATOR,SELECTEDBRAND))),
      "Col2='"&SELECTEDBRAND&"'",
      JOIN(" OR ","Col2='"&SPLIT(SELECTEDBRAND,SEPARATOR)&"'")
    )
    &" GROUP BY Col2,Col3 LABEL Col3 '', SUM(Col4) ''",1),
  "SELECT Col1,SUM(Col2) GROUP BY Col1 ORDER BY Col1 ASC LABEL SUM(Col2) ''",0)
 ),
";"),"ORDER BY Col1 DESC",0)
)($A:$D,$G$2,","))