Using the Google Sheets Query function we can extract all the records, I mean rows, from the previous month.

Assume the current month is December 2019. You want to get all the rows from the previous month, i.e. November 2019. Then how to get it?

Whether it’s a current month or a previous month, you can filter data accordingly using Query. The required scalar functions are month(), year(), and now().

We can use the above functions in a date or DateTime column to filter the previous month’s records in Google Sheets.

Actually, a very similar topic is already explained on this blog – Sum Current Month Data Using Query Function in Google Sheets. But that doesn’t address the previous month.

Further unlike extracting the current month’s rows, in the previous month’s filtering, you will face one issue especially when the current month is January (explained in detail below).

So, I thought about writing this article. Let’s start with an example, as usual.

Get All the Rows from the Previous Month in Google Sheets Using the Query Function

Sample Data: Statement of Account (SOA) – Data Range is A1:F10.

Important Note: As per my system clock, the current month is December.

If you are going to use my above 6-column table, you must change the dates in column 2, if the current month as per your system clock is not December.

In such a case, please change months # 11 (previous month) and 12 (current month) based on the month on your system clock. Also if the year is different, change the year too.

Query to Get Prior Month Rows in Google Sheets

It’s the simplest solution and won’t work if the current month is January.

The following Google Sheets Query formula will extract all the rows from the previous month (the rows containing the previous month’s dates in the second column in the range).

Please do note that the above table is in the range A1:F10 in the sheet named ‘SOA’.

You can use the below Query to get the prior month’s records/rows in the same sheet or any sheets in the same file.

=query(SOA!A1:F,"Select * where month(B)=month(now())-1",1)

Here is the explanation of the syntax of the above Query formula.

1. Select * returns all the columns. Just want 1-2 columns? Then, for example, use Select A,E to get the INV # and BALANCE columns.
2. To get the previous month in Query, we can dynamically use month(B)=month(now())-1 in the WHERE clause.
3. The number 1 in the last part is for returning the header row with the output. If you don’t want the header row, modify the range as SOA!A2:F and set 1 to 0.

How do we solve the January Issue then?

Query to Get Last Month’s Rows Taking Account of the Year Also

In the above Query to filter the previous month’s data, I have used a sample ‘Statement of Account’ (SOA). It’s a small set of data.

If you use a large set of sales data with several rows, there may be rows containing the same month but in different years.

For example records of 01/12/18 and 05/12/19. In such cases, we must include the year() scalar function to further improve the Query filter.

We normally think we can use the Query as below using the year() scalar function.

=query(SOA!A1:F,"Select * where month(B)=month(now())-1 and year(B)=year(now())",1)

But it will return the wrong outputs if the current month is January!

Can you explain the reason? Yes! Read that below.

Google Sheets Query to Extract All the Rows from the Previous Month When the Current Month Is January

If the current month is January, the year(B)=year(now()) won’t match. It should be year(B)=year(now())-1.

In all the other months we can use the above comparison – the former one, i.e. year(B)=year(now()).

Similarly, the month(now())-1 won’t work.

Then how to dynamically solve the above previous month and year filter issue?

You can use IF logical in Google Sheets Query as explained earlier here – How to Use IF Function in Google Sheets Query Formula.

But the above-linked tutorial is not coded for our above filter that involved month and year. Here is the IF logic that we can use in Query here.

=if(month(today())<>1,"month(B)=month(now())-1 and year(B)=year(now())","month(B)=11 and year(B)=year(now())-1")

We can use the above IF statement within Query as below.

=query(SOA!A1:F,"Select * where "&if(month(today())<>1,"month(B)=month(now())-1 and year(B)=year(now())","month(B)=11 and year(B)=year(now())-1"),1)

Update: Here is a new formula.

=query(SOA!A1:F,"Select * where month(B)="&month(eomonth(today(),-1))-1&" and year(B)="&year(eomonth(today(),-1)))

This way we can use Google Sheets Query to extract all the rows from the previous month.

Sum Last Month’s Records

This is just an additional tip.

Assume you want to sum the credit amount column (SOA column D).

The formula to sum the total credit amount of last month that takes account of month and year.

Change select * to select D then wrap the entire Query with Sum.

=sum(query(SOA!A1:F,"Select D where "&if(month(today())<>1,"month(B)=month(now())-1 and year(B)=year(now())","month(B)=11 and year(B)=year(now())-1"),1))

This may also work.

=sum(query(SOA!A1:F,"Select D where month(B)="&month(eomonth(today(),-1))-1&" and year(B)="&year(eomonth(today(),-1))))

So simple, right?