To sum by current week in Google Sheets, first, you should know how to filter dates that fall in the current week. Once you master this filter trick, you can play around with that.
From the filtered dates, you can perform different types of calculations like sum by current week, sum by weekdays, etc.
So what I am going to do is first providing you a formula that filters the current week’s dates or rows that containing the current week’s dates. Then I will explain to you how to sum by current week in Google Sheets.
Similar: Filter Data for Certain Number of Weeks in Google Sheets Using Formula
How to Filter Current Week in Google Sheets
Sample Data:
In this dataset, I’ve highlighted the current week’s dates considering today’s date as of Sun, 17 Jun 2018.
If you follow my above dataset, please don’t stick with the dates in Column A. You should change that dates in column A, as the current week keeps changing as days go by.
I have considered the week starts from Sunday to Monday. If you want it from Monday to Sunday, you can adjust the formula that I am going to provide. I will explain that also.
The Formula to Filter Current Week’s Dates in Google Sheets
The following formula can filter the above-highlighted rows.
=filter(A3:C,weeknum(A3:A)=weeknum(today()))
The result would be the above-highlighted rows. If you want to start the weeks on Monday and end on Sunday, then tweak the formula as below.
=filter(A3:C,weeknum(A3:A,2)=weeknum(today(),2))
Here I’ve used the functions Filter, Weeknum and Today. Learn these functions in detail – Learn All the Popular Google Sheets Functions. Now let me tell you how to sum by current week.
Sum by Current Week in Google Sheets
The output of the above formula is as follows.
Actually, for summing the column C amount based on the current week, column A and B values are not required in the filtered output.
But I’ve included the values in those columns for some reasons. In later if you want to conditionally sum current weeks data, this output will be useful.
If you just want to sum by current week, just change the above filter formula as below.
=filter(C3:C,weeknum(A3:A)=weeknum(today()))
Here the filter range A3:C has been changed to C3:C. So this filter formula will only return the values in Column C that falls in the current week.
Now you can just wrap this formula with the Sum and that’s the formula to sum by current Week in Google Sheets.
=sum(filter(C3:C,weeknum(A3:A)=weeknum(today())))
That’s all. Hope you have enjoyed the stay!
Related Google Sheets Tutorials:
Is there a way to make it like your post on sum by month?
Hi, Rik,
I’ll try. Can you include a link to your demo/mockup data in Google Sheets via comment? Just data in a few rows and the result you want (entered manually).
Hello,
Thank you SO MUCH for this article!!!
You say…
=filter(A3:C,weeknum(A3:A,2)=weeknum(today(),2))
If I wanted to start the week on a Tuesday what would I need to change?
I tried changing 2 to 3 but I got an error.
Thank you for your help.
Hi, Terry,
The number to use to filter dates that fall on Tuesday-Wednesday is 12, not 3.
=filter(A3:C,weeknum(A3:A,12)=weeknum(today(),12))
Please check WEEKNUM function in my tutorial related to Sheets Date functions.
Best,