Using ARRAYFORMULA with SUMIF for multiple conditions combined with conditions using a wildcard. Result by Months

Question

This is a similar problem to Using ARRAYFORMULA with SUMIF for multiple conditions combined with a wildcard to select all values for a given condition, but trying to get the sum of the first column by all months of a given year. I was trying to extrapolate the same solution provided by @player0, but group by doesn't work because I would like to get a result for each month of the year, regardless of the month's data on the source. Here is the sample:

In column G we have different filter conditions and it can include a special token: ALL to include all values for each criterion. For the year we can select the given year for which we want to sum the result by each month of the year.

Column I has the expected result using the similar idea of the referred question, but it cannot be expressed in an ArrayFormula (query result doesn't expand):

=IFNA(QUERY(QUERY(FILTER($A$2:$E, 
 IF($G$2="All", $B$2:$B<>"×", $B$2:$B=$G$2), 
 IF($G$4="All", $C$2:$C<>"×", $C$2:$C=$G$4), 
 IF($G$6="All", $D$2:$D<>"×", $D$2:$D=$G$6),
 YEAR($E$2:$E) = $G$8
 ), 
 "select sum(Col1) where month(Col5) =" & MONTH($H2) - 1), 
  "offset 1", 0),"")

On column J the array try doesn't work because we cannot use the virtual range with SUMIF it can be resolved by creating auxiliary columns with the FILTER, but I am looking for a solution that doesn't require that.

=Arrayformula(if(not(ISBLANK(H2:H)), sumif(
Filter(FILTER($A$2:$E, IF($G$3="All", $B$2:$B<>"×",
 $B$2:$B=$G$3), IF($G$5="All", $C$2:$C<>"×", $C$2:$C=$G$5), 
 IF($G$7="All", $D$2:$D<>"×", $D$2:$D=$G$7), 
 YEAR($E$2:$E) = $G$9),{1,0,0,0,0}), H2:H,
Filter(FILTER($A$2:$E, IF($G$3="All", $B$2:$B<>"×", 
 $B$2:$B=$G$3), IF($G$5="All", $C$2:$C<>"×", $C$2:$C=$G$5), 
 IF($G$7="All", $D$2:$D<>"×", $D$2:$D=$G$7), 
  YEAR($E$2:$E) = $G$9), {0,0,0,0,1})
),))

Here is the sample spreadsheet: https://docs.google.com/spreadsheets/d/1Lqjim3c_j8KNr_7LVlKjlR4BXi9_1HFoGDuwsuX1XS0/edit?usp=sharing

Answer 1

try:

=INDEX(IFNA(VLOOKUP(MONTH(H2:H13), QUERY(QUERY(FILTER(A2:E, 
 IF(G3="All", B2:B<>"×", B2:B=G3), 
 IF(G5="All", C2:C<>"×", C2:C=G5), 
 IF(G7="All", D2:D<>"×", D2:D=G7)), 
 "select month(Col5)+1,sum(Col1) 
  where Col1 is not null "&IF(G9="",," 
    and year(Col5)="&G9)&  
 "group by month(Col5)+1"), 
  "offset 1", 0), 2, 0)))

Answer 2

This formula is in cell L2 of your sample Sheet1. It is a sumif() that uses a regex and MMULT to create an array of 3's where the conditions are met and &'s it with the month for the 'sumif' criterium.

=ARRAYFORMULA(SUMIF(MMULT(N(REGEXMATCH(B2:D,SUBSTITUTE({G3,G5,G7},"ALL",))),
 {1;1;1})&EOMONTH(E2:E,-1)+1,3&H2:H13,A2:A))

Detail Explanation (The formula, then in the following lines the explanation)

SUBSTITUTE({G3,G5,G7},"ALL",)

Generate a 1x3 array with G3,G5,G7 values, if "ALL" then will be replaced by empty string. In case of ("ALL", ALL", "ALL") returns: (,,). The result of SUBSTITUTE will be the regular expression to be used in REGEXMATCH. In case of ("ALL", ALL", "ALL") REGEXMATCH will return (TRUE, TRUE, TRUE) on each row.

REGEXMATCH(B2:D,SUBSTITUTE({G3,G5,G7},"ALL",))

Return an array of the same size as B2:D (let's say Mx3 array) where the condition are met (TRUE|FALSE) values and the function N()converts it into (0|1) values.

MMULT(N(REGEXMATCH(B2:D,SUBSTITUTE({G3,G5,G7},"ALL",))),
 {1;1;1})

Multiplies two matrixes: Mx3 x 3x1 and returns an array Mx1. If all filters conditions are satisfied will return on a given cell the number 3 (we have three conditions), otherwise a number lower than 3.

EOMONTH(E2:E,-1)+1

Calculate the last day of the previous month of the cells E2:E and add one day resulting the first day of the month of E2:E. We need to compare such dates with the dates in H2:Hthat represent the first day of the month.

SUMIF(range, criterion, [sum_range])

• range: will contain an array of Mx1 with one of the values: {0,1,2,3} depending on how many conditions are met. It appends &EOMONTH(E2:E,-1)+1. Remember dates are stored as an integer number.

• criterium: 3&H2:H13the reference dates prefixed by number 3.

• sum_range: The range A2:A we want to sum based on the range and criterium match, so only rows with 3 values of MMULT will be considered.

Here the result in case of filters have the value ALL:

Here the solution when ALL token is not used:

Note: The only difference with expected result and @player0 solution is that it returns 0when there is no match.