Using ARRAYFORMULA with SUMIF for multiple conditions combined with conditions using a wildcard. Res-CodePudding

This is a similar problem to

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:

CodePudding user response：

This formula is in cell I1 of your sample sheet. I believe you have been asking about this problem across a few different help forums. Please cross link your posts.

=ARRAYFORMULA(QUERY({A1:A,N(REGEXMATCH(TEXT(B:E,{"","","","yyyy"}),SUBSTITUTE({G3,G5,G7,G9},"ALL",))),1*TEXT(E1:E,"mmmyyyy");SPLIT("0|1|1|1|1|"&DATE(G9,SEQUENCE(12),1),"|")},"select Col6,SUM(Col1) where Col2 Col3 Col4 Col5=4 group by Col6"))