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. If you ask me for an array formula to conditionally sum date ranges in Google Sheets, I would recommend MMULT.

Why don’t we use Sumifs to sum a date range?

The reason for not using Sumifs is 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 not useful when the criteria are date ranges.

You May Like: Sumif Multiple Columns Criteria – It Works in Google Sheets.

Multiple Date Ranges as Criteria in Sumifs

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

So wee need to find an alternative solution. I mean an array formula to conditionally sum date ranges and that is the MMULT one.

Earlier I have discussed how to expand Sumifs result in Google Sheets using alternative functions. In that also, I didn’t include how to conditionally sum multiple dates ranges in Google Sheets. I thought I should discuss that in detail and this tutorial is as a result of that thought process.

Must Read: Google Sheets: Sumifs Array Formula Expanding Issue and Alternative Formulas.

The array formula that I am going to use to conditionally sum values in a column if the date is between two dates, is MMULT oriented. Before going to that see the non-array Sumifs formula that conditionally sums a date range.

=sumifs($D$2:$D,$C$2:$C,I2,$B$2:$B,">="&G2,$B$2:$B,"<="&H2)

Enter this formula in cell J2 and drag down.

Must Read: How to Include a Date Range in SUMIFS in Google Sheets.

Instead of Sumifs, you can use MMULT in cell J2 which is an array formula to conditionally sum date ranges in Google Sheets. So no need to drag that formula down to cover additional ranges.

Array Formula to Conditionally Sum Multiple Dates Ranges

As you can see, I have used MMULT to conditionally sum multiple date ranges. Here is that 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/ranges.

Syntax:

MMULT(matrix1, matrix2)

Must Check: Google Sheets Functions Guide.

I have used the comparison operators =, <= as well as >= to form two matrices as below.

How the MMULT be able to conditionally sum date ranges in Google Sheets? See the formula explanation below.

MMULT for Conditional Sum in Google Sheets

Formula Explanation:

In the formula explanation, I am going to use finite ranges. So 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 the Matrix 1 and the last part forms the Matrix 2 (see the above image).

Here is that matrices.

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

Formula that Forms the 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 as above. In that output, the numbers 1 in the first row means the following conditions are met (see the range G2:I2 in the first screenshot).

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

Second Row (see the range G3:I3 in the first screenshot):

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

I know you may have a few questions related to Matrix 1 in your mind. See that Q&A.

Q&A Related to Matrix 1

Question: Why I have transposed (changed 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 asterisk between each test?

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

The next question is related to the use of the function N in Matrix 2. I have used it to return the value 0 if the cell in the range is blank.

With this tutorial, I hope, I could answer your following queries.

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

Thanks for the stay. Enjoy!