Array Formula to Conditionally Sum Date Ranges in Google Sheets

To Sum a column in Google Sheets, if the date is between a start date and end date, we can use Sumifs. It’s called conditionally sum a date range.

Why not use Sumifs to sum a date range?

The reason for not using Sumifs is that it doesn’t expand the results like Sumif, its ‘sibling’. But Sumif has one restriction. It can expand the result using the ArrayFormula, but the conditions must be from a single column.

Again we can overcome that Sumif restriction by virtually combining the columns and criteria. But it’s not useful when the criteria are date ranges.

Multiple Date Ranges as Criteria in Sumifs

We can’t use Sumifs to expand the result for date ranges as shown in the image below.

So we need to find an alternative solution. And that solution is the MMULT formula.

Earlier, I discussed how to expand Sumifs results in Google Sheets using alternative functions. However, I didn’t include how to conditionally sum multiple date ranges in Google Sheets. So let’s dive into the details in this tutorial.

Array Formula to Conditionally Sum Multiple Date Ranges

As you can see in the image below, I have used the MMULT formula to conditionally sum multiple date ranges.

Here is the powerful formula:

=ArrayFormula(if(len(G2:G),(mmult((I2:I=transpose(C2:C))*((transpose(B2:B)>=G2:G)*(transpose(B2:B)<=H2:H)),N(D2:D))),))

In Google Sheets, the MMULT function calculates the matrix product of two matrices specified as arrays or ranges.

Syntax:

MMULT(matrix1, matrix2)

I have used the comparison operators =, <=, and >= to form two matrices. The MMULT formula is able to conditionally sum date ranges in Google Sheets. Let me explain the formula below.

MMULT for Conditional Sum in Google Sheets

Formula Explanation:

In the formula explanation, I am going to use finite ranges. So, there’s no need to use the IF + LEN formula in the beginning (the IF + LEN combo is famous in Google Sheets for limiting the Array Formula outputs to non-blank cells).

See that version of the above MMULT formula for finite ranges:

=ArrayFormula(mmult((I2:I3=transpose(C2:C10))*((transpose(B2:B10)>=G2:G3)*(transpose(B2:B10)<=H2:H3)),N(D2:D10)))

I am splitting this formula into four parts. The first three parts form Matrix 1, and the last part forms Matrix 2.

Here are the matrices:

In this, Matrix 1 handles the conditions, and Matrix 2 is the sum column.

Formula that Forms Matrix 1:

=ArrayFormula((I2:I3=transpose(C2:C10))*((transpose(B2:B10)>=G2:G3)*(transpose(B2:B10)<=H2:H3)))

This formula returns a two-row output. In that output, the numbers 1 in the first row mean that the following conditions are met:

Product: Apple
Date between 02/01/2019 and 07/01/2019

The second row means:

Product: Banana
Date between 04/01/2019 and 09/01/2019

You may have a few questions related to Matrix 1. Let me answer a couple of them.

Q&A Related to Matrix 1

Question: Why did I transpose (change the orientation) the data to test the condition?

Answer: I can test the range C2:C10 with the condition in I2 as below:

=ArrayFormula(C2:C10=I2)

It would return TRUE/FALSE in a column. But I have the conditions in I2:I3. So this formula doesn’t work.

=ArrayFormula(C2:C10=I2:I3)

You should either use it as:

=ArrayFormula(C2:C10=transpose(I2:I3))

or

=ArrayFormula(transpose(C2:C10)=I2:I3)

We need the latter to use in MMULT.

Question: What is the use of the asterisk between each test?

Answer: It’s equal to the AND logical operator. It means all the conditions must match (returns 1).

In conclusion, with this tutorial, I hope I could answer the following queries:

1. Is there an array formula to conditionally sum date ranges in Google Sheets?
2. How to sum if the dates are between a start and end date?
3. How to use the MMULT formula as an alternative to Sumifs?
4. How to use the MMULT formula to sum values between multiple date ranges?

Thanks for staying with me. Enjoy!