This post explains how to create rolling months backward (from today’s date) summary report in Google Sheets.

In the summary report, we can use either of the aggregation functions sum, average, count, max, min or all of them.

We need at least two columns of data to create the rolling months backward summary report in Google Sheets. They are a date column and a number column.

Sample Data: My sample data is in around 504 rows. So it’s not possible for me to post the ‘full’ screenshot or include it as a table within this post. Here is the first page for your quick glance.

Also, there is one more reason for not providing you the sample data.

Since the calculation is based on rolling backward months, the current date has an important role in the sample data. So in a future date, my sample data won’t be suitable for the test or in line with my explanation below.

You can create a proper sample data to test as above by inserting the below formula in cell A3 which will insert dates in the array/range A3:A503 (the range may slightly vary).

=SEQUENCE(
     days(today(),today()-500)+1,
     1,
     today()-500
)

Then fill the next column (B3:B503) with some random numbers.

Let’s go to the steps to create a rolling n months backward summary report in Google Sheets.

Introduction

Assume today’s date is 20-Apr-2020 and we have a table in A3:A in that A3:A contains the dates from 01-Feb-2020 to 20-Apr-2020.

The dates that fall in February will be summarized under rolling month 2, March will be under rolling month 1, and April will be under rolling month 0.

I hope you could understand this. Now the question here is how to identify the rolling month numbers backward from a date range as above in Google Sheets.

To find the rolling month number of any date from today’s date backward we can use the DATEDIF function. But not as per the standard use.

Must Read: How to Utilise Google Sheets Date Functions [Complete Guide].

Why the DATEDIF Function Alone is Insufficient to Find Rolling Month Numbers Backward

Assume the date in cell A3 is 24-Mar-2020. As per my earlier example, the rolling backward month number of this date should be 1 as it’s one of the dates from the last month (current month number [April] will be 0).

But the following DATEDIF won’t return 1, instead it would return 0!

Syntax: DATEDIF(start_date, end_date, unit)

Formula:

=datedif(A3,today(),"M")

Note: If you try to test this formula, with the same date in cell A3, in a future date (as per my system clock today is 20-Apr-2020), you would probably get a different number. So leave testing it. Please just try to concentrate on my explanation.

Why the DATEDIF is returning 0 instead of 1?

For DATEDIF, from 21-Mar-2020 to 20-Apr-2020 is the month # 0 as it relies on the number of whole months between a start_date and end_date, not calendar months.

Any workaround to sort this out?

DATEDIF and EOMONTH to Return Proper Backward Rolling Month Numbers in Google Sheets

If you know how to get backward rolling month numbers, then creating a rolling month backward summary report would be very easy using the Query function in Google Sheets.

I’ve already explained the DATEDIF issue above. So let’s find the solution.

Solution: Convert all the dates to month start date.

Generic formula as per the said solution above:

=DATEDIF(
     month_start_date_of_the_date_of_which_to_find_the_rolling_backward_month_number,
     month_start_date_of_today(),
     "M"
)

The cell A3 contains the date 24-Mar-2020. Convert it to the month-starting date (01-Mar-2020) using the EOMONTH formula as below.

=eomonth(A3,-1)+1

Note: If you are new to the above EOMONTH or any other function mentioned or going to mention in this post, please check my Google Sheets Function Guide.

The following formula will return the month starting date of the ‘current’ month (01-Apr-2020).

=eomonth(today(),-1)+1

So the DATEDIF formula to return the correct rolling month backward number of the date in cell A3 will be as below.

=DATEDIF(
     eomonth(A3,-1)+1,
     eomonth(today(),-1)+1,
     "M"
)

For the entire date in A3:A, we can use it as an array formula.

=ArrayFormula(
     DATEDIF(
        eomonth(A3:A,-1)+1,
        eomonth(today(),-1)+1,
        "M"
     )
)

Query to Make Summary of Rolling N Months Backwards from Today in Google Sheets

We have already completed the ‘complicated’ part of our tutorial. Now we can easily create the rolling months backward summary in Google Sheets.

I am going to use the Query function for this and here is the syntax.

Syntax: QUERY(data, query, [headers])

Our ‘data’ is in A3:B. In this, we should replace A3:A with our DATEDIF formula and B3:B with the actual values in B3:B. So we must use A3:B (data) as below.

={ArrayFormula(
     DATEDIF(
        eomonth(A3:A,-1)+1,
        eomonth(today(),-1)+1,
        "M"
     )
),B3:B}

Summary of Rolling Months Backward (Formula # 1):

Here the Query formula uses the above ‘data’ to group column 1, i.e. the backward month numbers and aggregate column 2.

=QUERY(
     {ARRAYFORMULA(
        DATEDIF(eomonth(A3:A,-1)+1,eomonth(today(),-1)+1,"M")
     ),
     B3:B},
     "select Col1,SUM(Col2),AVG(Col2),MIN(Col2),MAX(Col2)
      where Col2 is not null
      group by Col1
      order by Col1 desc
      label Col1'Rolling Month'"
)

I have used the aggregation functions SUM, AVG, COUNT, MIN, and MAX in the formula above. You can remove the unwanted ones.

Related: How to Sum, Avg, Count, Max, and Min in Google Sheets Query.

Limit the Months to N (Two Methods)

Method 1: By including one more condition in the WHERE clause.

Assume you want past 12 months’ rolling backward month-wise summary. That means the ‘n’ here is 12.

Here is the formula (formula # 2) for rolling twelve months backward summary in Google Sheets.

=QUERY(
     {ARRAYFORMULA(
        DATEDIF(eomonth(A3:A,-1)+1,eomonth(today(),-1)+1,"M")
     ),
     B3:B},
     "select Col1,SUM(Col2),AVG(Col2),MIN(Col2),MAX(Col2)
      where Col2 is not null and Col1<12
      group by Col1
      order by Col1 desc
      label Col1'Rolling Month'"
)

Compared to formula # 1, and Col1<12 is the additional condition used in the WHERE clause. In this the <12 controls the ‘n’.

Method 2: By filtering the dates.

Instead of the additional condition in the WHERE clause, you can first extract the required data range by filtering it and then use my formula # 1 in that range.

For filtering you can refer my guide – Formula to Filter Rolling N Days | Months in Google Sheets.

That’s all about the rolling months backward summary in Google Sheets. Enjoy!

Sample Sheet

Resources:

• How to Group Data by Month and Year in Google Sheets.
• Sum by Month in Google Sheets Using Combined SUMIF Formula.
• Create Month Wise Summary in Google Sheets Using Query Formula.
• Month, Quarter, Year Wise Grouping in Pivot Table in Google Sheets.
• Google Sheets SUMIF to Sum by Month and Year [How To Guide].
• Filter by Month and Year in Query in Google Sheets.
• Query to Create Daily/Weekly/Monthly/Quarterly/Yearly Report Summary in Google Sheets.
• Sum Current Month Data Using Query Function in Google Sheets.