The function SUMIF helps you to conditionally sum your data range. This tutorial, SUMIF to Sum by Current Work Week, will be useful if you want to sum a column based on another date column.
In this Google Sheets tutorial, the current work week is considered as the weekdays from Monday to Friday. So there will be a total of 5 days that excluding Saturday and Sunday.
Here are my two column data and the function SUMIF to sum by current work week in Google Sheets.
Master Formula:
=arrayformula(SUM(sumif(A1:A,TODAY()-WEEKDAY(TODAY(),2)+row(A1:A5),B1:B)))
Note: Today’s date is 21/08/2018. The current week here is as per this date. As you know this date range will keep on changing. But the formula will be the same!
I mean you can use the same SUMIF formula at a future date. It can automatically adjust to that times current work week.
SUMIF to Sum By Current Work Week – Formula Explanation
Here I am detailing each part of the above SUMIF formula so that you can learn it for your use. Find the above master formula explanation under the below subtitle.
How to Sum By Current Work Week in Google Sheets?
SUMIF Syntax:
SUMIF(range, criterion, [sum_range])
In my formula, the range is A1: A which is a date range. Obviously, this range is tested against ‘criterion’ and that criterion is current Work Week. The sum range is the second column, B1: B.
What you want to learn is the ‘criterion’ part. See that in detail.
How to test Current Work Week in a Column in Google Sheets?
This is that criterion in the formula.
TODAY()-WEEKDAY(TODAY(),2)+row(A1:A5)
I know you are going to try this formula in your sheet. But it won’t work. You should wrap this date functions with the ArrayFormula as below.
=ArrayFormula(TODAY()-WEEKDAY(TODAY(),2)+row(A1:A5))
This generates a date range containing the dates in the current work week. Try it on your sheet. You can see that the formula populates a date range from Monday to Friday.
I am again going to split this formula to make you understand it properly.
It’s a combination of two formulas.
Combination 1:
=TODAY()-WEEKDAY(TODAY(),2)
This formula returns a single date and that date is the start date in the current week. That would be Sunday. How?
Check This: Formula to Sum by Current Week in Google Sheets
Combination 2:
I am entering the combination formula1 in cell H2 and Combination formula 2 in cell I2.
See the output in I2: I6 and that are the criteria in my SUMIF formula to sum by current work week.
That’s all about Sum by current work week in Google Sheets.
I want to sum by current week conditionally as above. But I have additional columns to test. I mean, can I use SUMIFS in the same way to sum the current work weeks’ total?
As far as I know, it’s not possible. The simplest way is by using Query.
Must Check: Conditionally SUM Current Work Week Range Using Query (Multiple Conditions)
Related Reading:
1. How to Calculate the Moving Sum of Current Week in Google Sheets.
2. SUMIF Formula to SUM Current Week’s Data in Google Sheets.
3. Find the Date or Date Range from Week Number in Google Sheets.
