=ArrayFormula Using SumIf Function to Sum Between Two Dates in Google Sheets

user_17171162 0
Viewed 328

I am trying to create a function that adds up all of the miles driven between two dates while using the =arrayformula function to iterate down the column the results

Here is the syntax for my function below:

'TempDataSet'!G2:G: sum range that has the total miles driven

'TempDataSet'!C2:C: criteria column that has all the dates driven

A7: Start Date

B7: End Date

This is the function I wrote below using the sumifs function

=SUMIFS('TempDataSet'!$G$2:$G,'TempDataSet'!$C$2:$C,">="&A7,'TempDataSet'!$C$2:$C,"<="&B7)

This function works, however I would like to implement the =arrayformula function so that the function can auto drag down the results.

I know **you can't use =sumifs while inside an =arrayformula, so this is where I am stuck

What is the way to do this using the =sumif function inside of an =arrayformula?

I've tried also using the =query function, but =arrayformula is not support with the =query function

sumifs array-formulas google-sheets-formula google-sheets
2 Answers

Use MMULT (if data are between row#2 AND #10)

=mmult(transpose(arrayformula(
IF((TempDataSet!$C$2:$C$10>=TRANSPOSE(A7:A))*(TempDataSet!$C$2:$C$10<=TRANSPOSE(B7:B)),
TempDataSet!$G$2:$G$10,0))),
sequence(rows(TempDataSet!$G$2:$G$10),1,1,0))

if you limit the number of rows in TempDataSet to avoid long calculations, you can also use

=mmult(transpose(arrayformula(
IF((TempDataSet!$C$2:$C>=TRANSPOSE(A7:A))*(TempDataSet!$C$2:$C<=TRANSPOSE(B7:B)),
value(TempDataSet!$G$2:$G),0))),
sequence(rows(TempDataSet!$G$2:$G),1,1,0))

Explanation

  • create a first matrix of conditions

    =arrayformula(IF((TempDataSet!$A$2:$A$10>=TRANSPOSE(A2:A7))*(TempDataSet!$A$2:$A$10<=TRANSPOSE(B2:B7)),TempDataSet!$B$2:$B$10,0))

enter image description here

  • create a sequence of 1 : sequence (the number of columns is 1 and the number of rows is equal to the number of columns with the matrix that I have transposed)

    =sequence(rows(TempDataSet!$B$2:$B10),1,1,0)

  • finally apply MMULT : MMULT

user_150193800

try:

=SUMPRODUCT(FILTER('TempDataSet'!G2:G; 'TempDataSet'!C2:C>A7; 'TempDataSet'!C2:C<=B7))
user_56326290
Related