Using ARRAYFORMULA with SUMIF for multiple conditions combined with a wildcard to select all values for a given condition

Question

I am using ARRAYFORMULA with multiple conditions using SUMIF concatenating the conditions using & and it works. Now I would like to use similarly this idea for a special condition indicating to consider all values using a wildcard ("ALL") for a given column, but it doesn't work.

Here is my example:

On I2if have the following formula:

=ARRAYFORMULA(if(not(ISBLANK(H2:H)),sumif(B2:B & C2:C & D2:D & year(E2:E),
  if($G$2="ALL",B2:B,$G$2) & if($G$4="ALL",C2:C,$G$4) & if($G$6="ALL",D2:D,$G$6) &
  H2:H,A2:A),))

and it works, when I enter specific values, but when I use my wildcard: ALL indicating that for a given column/criteria all values should be taken into consideration, it doesn't work as expected. The scenario should consider that all criteria can be labeled as ALLin such case it will provide the sum of NUM per year.

Here is my testing sample in Google Spreadsheet:

https://docs.google.com/spreadsheets/d/1c28KRQWgPCEdzVvwvXFOQ3Y13MBDjpEgKdfoLipFAOk/edit?usp=sharing

Notes:

1. I was able to get a solution for that using SUMPRODUCT but this function doesn't get expanded with ARRAYFORMULA
2. In my real example I have more conditions, so I am looking for a solution that escalates having more conditions

Answer 1

use:

=QUERY(QUERY(FILTER(A2:E, 
 IF(G2="All", B2:B<>"×", B2:B=G2), 
 IF(G4="All", C2:C<>"×", C2:C=G4), 
 IF(G6="All", D2:D<>"×", D2:D=G6)), 
 "select year(Col5),sum(Col1) 
  where Col1 is not null
  group by year(Col5)"), 
  "offset 1", 0)