Are you struggling to conditionally sum your data range in Google Sheets? Look no further! In this tutorial, we’ll explore how to use the SUMIF function to sum a column based on another date column, specifically the current work week.
The Current Work Week in Google Sheets
Before we dive into the solution, let’s define what we mean by the current work week. In this context, it refers to the weekdays from Monday to Friday, excluding Saturday and Sunday. So, essentially, we’re looking at a total of 5 days.
To make it easier to understand, let’s take a look at an example of two columns of data and the SUMIF function in action.
Master Formula:
=arrayformula(SUM(sumif(A1:A,TODAY()-WEEKDAY(TODAY(),2)+row(A1:A5),B1:B)))
Please note that today’s date is 21/08/2018. The current week is determined based on this date. However, keep in mind that the date range will change as time goes on, but the formula will remain the same!
In other words, you can use the same SUMIF formula in the future, and it will automatically adjust to the current work week at that time.
SUMIF: Formula Explanation
Now, let’s break down the different parts of the SUMIF formula for a better understanding.
How to Sum by the Current Work Week in Google Sheets?
The syntax for the SUMIF function is as follows:
SUMIF(range, criterion, [sum_range])In our formula, the range is A1:A, which represents a date range. This range is tested against the “criterion,” which is the current work week. The sum range is the second column, B1:B.
What you really want to learn is the “criterion” part. Let’s take a closer look.
How to Test the Current Work Week in a Column in Google Sheets?
The criterion in the formula represents the current work week. Here’s the formula:
=TODAY()-WEEKDAY(TODAY(),2)+row(A1:A5)Before you rush to try this formula in your own sheet, please note that you need to use the ArrayFormula function to make it work correctly. Here’s the modified formula:
=ArrayFormula(TODAY()-WEEKDAY(TODAY(),2)+row(A1:A5))This formula generates a date range that includes the dates of the current work week. Give it a try in your own sheet, and you’ll see that it populates a date range from Monday to Friday.
Now, let’s break down this formula further.
It’s actually a combination of two formulas:
Combination 1:
=TODAY()-WEEKDAY(TODAY(),2)This formula returns a single date, which represents the start date of the current week. In this case, it would be Sunday. How interesting!
Combination 2:
=ArrayFormula(TODAY()-WEEKDAY(TODAY(),2)+row(A1:A5))I’ve entered Combination 1 in cell H2 and Combination 2 in cell I2. The output in cells I2 to I6 represents the criteria in my SUMIF formula to sum by the current work week.
That’s all you need to know about summing by the current work week in Google Sheets.
But wait, what if you have additional columns to test? Can you use SUMIFS in the same way to sum the total for the current work week? Unfortunately, it’s not possible. The simplest solution is to use the Query function.
For more related reading, check out the following articles:
- How to Calculate the Moving Sum of the Current Week in Google Sheets
- SUMIF Formula to SUM the Current Week’s Data in Google Sheets
- Finding the Date or Date Range from Week Number in Google Sheets
So there you have it! Now you can confidently use the SUMIF function to sum your data by the current work week in Google Sheets. For more tips, tricks, and tutorials, visit Crawlan.com.
Happy sheeting!
