I have a dataset, and the header row of it contains dates as field labels (column name). Below that header row, I have some values. In this case, how to sum the last 7, 30, 60, or n days total from today’s date in each row in Google Sheets.
If you are familiar with the SUMIFS and TODAY functions, you may be able to sum the last n days in each row in Google Sheets.
When you use such a formula to calculate the last 7, 30, and 60 days total, you may require to copy it down the row as it won’t expand.
Let’s quickly go to one example to understand the data and my expected result.
Drag-Down Formula to Calculate Last 7, 30, and 60 Days Total from Today
The following SUMIFS formula in cell A3 calculates the last 7 days total for row 3, i.e., the range D3:3. As I have mentioned in the first paragraph, the dates are in D2:D, i.e., in the header row.
=sumifs(D3:3,$D$2:$2,">"&today()-7,$D$2:$2,"<="&today())
Note:- To populate dates in D2:M2 that suitable for the date range to test, you can use =sequence(1,10,today()-9) formula in D2 (first empty D2:M2). Then select D2:M2 and format (menu) it to Number > Date.
This SUMIFS formula requires a manual action to populate the result in the rows below. That means you should drag the fill handle in cell A2 down.
You can use the same SUMIFS formula to return the last 7, 30, and 60 days total from today in Google Sheets. How?
I have used -7 in the formula to return the last 7 days total from today. Just replace that number with -30, -60, or -90 as per your requirement.
The SUMIFS is not the only formula that we can use in Google Sheets for the said purpose. We can also use the SUM and FILTER combo as below.
The below SUM + FILTER combo is for cell A2. It’s again a drag-down formula.
=sum(filter(D3:3,$D$2:$2>today()-7,$D$2:$2<=today()))But one of the obstacles that you may face in the above scenario will be to automatically expand the formula from one row to the rows down. I have a solution for that too.
You May Like: Filter Data Based on This Week, Last Week, Last 30 Days in Google Sheets.
Array Formula to Get Last 7, 30, and 60 Days Total in Each Row in Google Sheets
Some of the Google Sheets formulas won’t expand. SUMIFS is one among them. The above FILTER and SUM combo is another example of it.
So what are the available options in front of us to total the last 7/30/60/90 days values in each row in Google Sheets?
The alternatives that come to my mind are MMULT and DSUM. Here we will use the latter one.
But that is not enough. With DSUM, we may require to use some other functions too. Here is that powerful formula!
Empty the range A3:A and insert my following DSUM in A3.
=ArrayFormula(dsum(transpose({C2:C7,filter({column(D2:M2)*0;D3:M7},D2:M2>today()-7,D2:M2<=today())}),sequence(rows(C3:C7),1,2),{"Item";0}))Using the above array formula, you can get the last 7, 30, 60, and 90 days total in each row in Google Sheets. In it, you should change -7 to the required number of days.
The above formula only covers a specific number of columns. In the last part of this tutorial, I’ll help you expand it for the entire column in the sheet.
Formula Logic and Explanation
Syntax: DSUM(database, field, criteria)
In our above DSUM, we have used combination formulas (expressions) as database, field, and criteria.
Database (Transposed Filtered Range)
The following FILTER in the above formula filters the columns that fall in the date range last 7, 30, 60, 90, or n days that you specify. It (partially) acts as the database in DSUM.
={C2:C7,filter({column(D2:M2)*0;D3:M7},D2:M2>today()-7,D2:M2<=today())}
The formula filters the range D3:M7 that falls within the given date range.
The column(D2:M2)*0 formula (highlighted in Cyan) inserts a row on the top of the result that contains the value 0. In addition to that, added the column range C2:C7 (highlighted in Yellow).
The above-added row and column require in the DSUM to use as the function argument field and criteria. You will understand that later.
TRANSPOSE the above-filtered result, and that is the (proper) database to use in DSUM.
=transpose({C2:C7,filter({column(D2:M2)*0;D3:M7},D2:M2>today()-7,D2:M2<=today())})Database:
Logic:-
We have transposed the filtered data because the DSUM is only capable of returning column totals as an array. We want row total as an array.
So, to calculate the last 7, 60, and 90 days total in each row, we have transposed the filtered data. Then we will calculate the column total, which will be equal to the row-wise sum.
In my tutorial How to Use the Sumif Function Horizontally in Google Sheets, I have discussed the same under the subtitle “Array Formula Alternative (DSUM Formula)”
Field (Sequential Numbers)
If you check the database above, you can understand that we require the total of the columns “A”, “B”, “C”, “D”, and “E”, and these are the columns from column # 2 to 6 in the database.
So the fields that we should use are the array ={2;3;4;5;6}. The below SEQUENCE does that part.
=SEQUENCE(rows(C3:C7),1,2)Criteria (Field Label and Condition)
In DSUM or other similar database functions, the first column will be the criteria column.
As per our database, the field label of the criteria column is “Item”, and the criterion is 0 as there is no other value in that column.
{"Item";0}I hope you could understand how the formula calculates the last 7, 30, and 60 days total in each row in Google Sheets.
Total of Last N Days in Each Row from Today and Infinite Columns
The purpose of the formula is about totaling the last 7, 30, 60, 90 days based on horizontal dates.
So, you may have more dates in the header row in the future that will expand the range. Right now, it’s only up to column M.
We can carefully modify the formula to include infinite columns and rows up to 1000 as below.
=ArrayFormula(dsum(transpose({C2:C1000,filter({column(D2:2)*0;D3:1000},D2:2>today()-7,D2:2<=today())}),sequence(rows(C3:C1000),1,2),{"Item";0}))Here are the changes I have made.
- C2:C7 became C2:C1000.
- D2:M2 became D2:2.
- D3:M7 became D3:1000.
The above formula will leave 0 values in blank rows down column A as a trail. To avoid that, we can include an IF test as below.
=ArrayFormula(if(C3:C="",,dsum(transpose({C2:C1000,filter({column(D2:2)*0;D3:1000},D2:2>today()-7,D2:2<=today())}),sequence(rows(C3:C1000),1,2),{"Item";0})))That’s all. Thanks for the stay. Enjoy!
