Using Google Sheets FILTER and QUERY combined to sum multiple columns

Question

I'm trying to sum the values when B:B = J2 AND C:C = K2 AND row 3:3 >= L2 AND row 3:3 is < M2

enter image description here

I'm using the following formulas

= sum(QUERY(FILTER($B$3:$H$13,$B$3:$H$3>=L2,$B$3:$H$3<M2), "select Col3,Col4,Col5,Col6 where Col1 contains'"&J2&"' and Col2 = date '"&TEXT(K2,"yyyy-mm-dd")&"'",0))

= sum(QUERY(FILTER($B$3:$H$13,$B$3:$H$3>=L2,$B$3:$H$3<M2), "select * where Col1 contains'"&J2&"' and Col2 = date '"&TEXT(K2,"yyyy-mm-dd")&"'",0))

First Problem: In the first formula, if I change the criteria values, it will change the number of columns on the FILTER and it will give and error because the query won't find the selected columns.

Second Problem: In the second formula, if I select all (*) columns it will give a sum of the entire row and I just want the sum of the values.

I've tried searching for similar questions but no luck so far.

Answer 1

Using Query is probably overkill for what you're trying to do. You can use SumProduct for something like this or when doing a two dimensional sumifs.

In your specific case, you can use this formula in some cell like J2 to get your results:

=SUMPRODUCT(D:H,(B:B=$J$2)*(C:C=$K$2)*($D$2:$H$2>=$L$2)*($D$2:$H$2<$M$2)) 

You can see an example in this spreadsheet.

Answer 2

try:

=SUM(FILTER(FILTER(D4:H, D3:H3>=L2, D3:H3<M2), B4:B=J2, C4:C=K2))