In every day or frequently updating dataset, the current week keeps changing as the days go on. If you want to calculate the moving sum of current Week in Google Sheets, you should depend on any custom formula. No default function is available.
It’s easy to sum the current week’s sales or any other transactions in Google Sheets. You can use the Filter, Query, or the function Sumifs for this.
In these functions, you can specify the current weeks start and end date as criteria for filtering and sum the required column.
But as the days go on the current week keeps changing. How to reflect that date changes in your formula?
Here is that tips to find the moving sum of current week in Google Sheets. You can set and forget my custom formula. You can have always the current weeks total at your fingertip.
The formula to Calculate the Moving Sum of Current Week in Google Sheets
Here is a sample sales data which is very simple to follow.
As per today’s date (21/06/2018), the dates that fall in current week are highlighted. Here the week starts on Sunday and ends on Saturday.
Note: I know some of you may want the week starts from Monday. In the formula part, I’ll explain to you how to achieve that.
As an example, if you are reading this post in 19/03/2019 the current week would be starting from 17/03/2019 and ending on 23/03/2019.
The moving SUM formula will automatically adjust the range to this date range. Here is that formula to calculate the moving Sum of current week in Google Sheets.
=query(filter(A2:B,weeknum(A2:A,1)=weeknum(today(),1)),"Select Sum (Col2) label Sum(Col2)''")
As per the current dataset, this formula would return the sum of the sales value as 317.00. It’s the sum of the range B4: B10.
You can use the above formula to find the moving sum of current week in Google Sheets. Now let me tell you how to change the weekday in this filter – Query combo.
=query(filter(A2:B,weeknum(A2:A,2)=weeknum(today(),2)),"Select Sum (Col2) label Sum(Col2)''")
Compare this formula with the above one. I’ve only changed the function Weeknum parameter 1 to 2 in this. Want to know more about the Weeknum and other date functions in Google Sheets? Then head to this post – Google Sheets Complete Date Functions.
Note: By any reason your dataset doesn’t contain any date that falls in the current week, the above moving sum formula would return an #N/A error.
To avoid this I suggest you wrap my above formula with an Iferror statement.
The Roll of Filter Function in Moving Sum Google Sheets Formula
With the help of the Weeknum function, the Filter formula extracts the current week’s data range as below.
Actually, the Weeknum is the key in this formula and that makes the weekly date range dynamic or moving.
My sample dataset only contains two columns and I want the sum of column 2. The Query is the easiest way to select the required column and sum. That’s what I’ve done.
Moving Sum – Additional Tip
Here is one conditional formatting tip. Do you know how to highlight current week’s data range on Google Sheets that keeps moving as the days go on?
Here is my custom formula that highlights the rows containing current week’s dates.
The formula to highlight current week data range in Google Sheets:
=weeknum($A3,2)=weeknum(today(),2)
How to apply this custom conditional formatting formula?
1. Go to the cell A2 where contain the first date in the data range.
2. Then go to the conditional formatting (format menu), apply the above custom formula.
Feel free to change the range A2: J50 to A2: some other range. That’s all about moving sum of current week in Google Sheets.
