CODEWITHSUNDEEP
regexgoogle-sheetsfilterspreadsheetgoogle-query-language
William Pontoppidan
William Pontoppidan

Reputation: 77

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

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

Can someone please help?

enter image description here

Upvotes: 1

Views: 143

Answers (2)

player0
player0

Reputation: 1

or:

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

enter image description here

Upvotes: 1

player0
player0

Reputation: 1

try:

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

enter image description here

Upvotes: 1

Related Questions