CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaspreadsheetarray-formulasgoogle-sheets-query
Zach Young
Zach Young

Reputation: 3

Using a ArrayFormula or Query to replace dragging down SumIf between dates

I would like to change one of the formulas I'm using to return an array of values instead of having to copy/paste or drag down the formula for each cell. My current formula that works for single cells that can be dragged down is:

=SUMIFS('Tracking Tasks'!B$2:B,
'Tracking Tasks'!C$2:C, ">="&B2,
'Tracking Tasks'!C$2:C, "<="&D2,
'Tracking Tasks'!D$2:D, "<>")

The formula sums up all points that are "Completed" between two dates that have also been "Released" (cell not blank). I've tried many solutions, however this appears to be closest to what I'm trying to achieve:

=ArrayFormula(SUM(
 QUERY('Tracking Tasks'!B$2:D,
  "select B 
  where C >= date '" & TEXT(B2:B, "yyyy-mm-dd") &
  "' and C <= date '" & TEXT(D2:D, "yyyy-mm-dd") & "'")
 ))

However, this solution doesn't return an array of values like I'd expect it to, and it doesn't account for the "Released" column.
Sample Google Sheet

Upvotes: 0

Views: 238

Answers (1)

Theza
Theza

Reputation: 613

Solution for you

=ArrayFormula(IFERROR(VLOOKUP(FLOOR((B2:B-2)/7),QUERY({IF('Tracking Tasks'!D$2:D<>"",FLOOR(('Tracking Tasks'!C$2:C-2)/7),""),'Tracking Tasks'!B$2:B},"select Col1,sum(Col2) where Col1>0 group by Col1"),2,0),""))

enter image description here

Upvotes: 2

Related Questions