Sum if month is equal to specified date month and if variables are included in list/range-CodePudding

I'm trying to create a formula to sum if month (column L) corresponds to date in C1 and D1 and if category (column M) is included in list (column I).

I solved the month criteria by using the following formula:

(C$1-$K$2)*12 month(C$2&1)

I cannot get the list criteria to work. I looked online for hours and tried

sumifs() - did not work with list as riteria
sumproduct(sumifs()) - only gave me one value from the list, which depended on positioning
arrayformula(sumifs())

Can someone please help?

CodePudding user response：

try:

=SUM(FILTER($N:$N, $K:$K=C1, $L:$L=MONTH(C2&1), 
 REGEXMATCH($M:$M, TEXTJOIN("|", 1, $I$2:$I))))

CodePudding user response：

or:

=INDEX(QUERY(K2:N, 
 "select sum(N) 
  where K="&D1&" 
    and L = "&MONTH(D2&1)&"
    and M matches '"&TEXTJOIN("|", 1, I2:I)&"'"), 2)