In Google Sheets, there is a powerful function called Query that allows you to conditionally sum the current work week range. Forget about complicated formulas and SUMIFS, the Query function is here to make your life easier and more flexible.
Let’s dive into the details of how to use Query to achieve this.
Conditions
First, let’s set the conditions for the sum:
- Dates in Column A should fall within the current work week.
- The items in Column B should be “Gravel 10-20 mm Beige”.
The range we’ll be summing is in Column C, which contains the number of trips.
Note: If you only have condition 1, I recommend using the SUMIF solution, which is cleaner and more elegant. Check out our tutorial on SUMIF to Sum By Current Work Week for more details.
Since we have multiple conditions and SUMIFS won’t cut it, the best way to find the answer is by using Query.
The Query Formula to Conditionally Sum Current Work Week Range
Here’s the formula you need:
=SUM(QUERY({filter({A2:A,B2:C},weeknum(A2:A)=weeknum(today()))},"Select Col3 where DayofWeek(Col1)>=2 and DayofWeek(Col1)<=6 and Col2='Gravel 10-20 mm Beige'"))The result? It’s 10!
But how does this Query formula conditionally sum the current work week range? Let me break it down for you.
Query Syntax
The Query function follows this syntax:
QUERY(data, query, [headers])Now, let’s take a closer look at each argument in relation to our Query formula.
1. DATA
In our Query formula, the data range is determined by the following FILTER formula:
{filter({A2:A,B2:C},weeknum(A2:A)=weeknum(today()))}This formula extracts the rows that contain dates from the current week in Column A. But wait, it’s the current week, not the current work week. This is where the magic of the WEEKNUM function comes into play.
To help you understand this, I’ve applied the filter in a blank range in my sheets. Take a look:
2. QUERY
The query in our formula goes like this:
"Select Col3 where DayofWeek(Col1)>=2 and DayofWeek(Col1)<=6 and Col2='Gravel 10-20 mm Beige'"This query allows us to sum the values in Column 3 (Number of Trips) if Column 2 items are “Gravel 10-20 mm Beige” and if the day of the week in Column 1 (date) falls within the range of Monday (2) to Friday (6).
The DayOfWeek scalar function is our secret weapon to filter the range down to the current work week.
3. HEADER
The header is optional, so I haven’t included it in my formula.
Additional Tips About this Query to Sum Current Work Week Range in Google Sheets
If you want to refer the Query condition “Gravel 10-20 mm Beige” to a cell, let’s say F10, you can modify the formula like this:
Col2='"&F10&"'I hope this tutorial has helped you learn how to use Query to sum the current work week range in Google Sheets. Enjoy your newfound spreadsheet expertise!
