0

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.

Improve this question

2 Answers 2

Reset to default
0

try:

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

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

enter image description here

Improve this answer

1 Comment

Yes, at first I was looking for a simple solution but not sure why I thought using query would work. Thank you, it works perfectly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.