Query to Sum Current Work Week Range in Google Sheets

Published on

In Google Sheets, the best function to conditionally sum current work week range is the Query.

The reason, it can handle multiple conditions similar to SUMIFS and is more flexible.

In the below demo data, I have highlighted the rows that contain the data in the current work week.

Sample data to test Query in current work week

The range is from Mon, 20 August 2018 to Fri, 24 August 2018. As per my system, today is Tue, 21 August 2018.

This sample data shows the date wise report of the number of trips (stone/aggregate material) transported to two different project sites.

Query to Sum Current Work Week Range in Google Sheets

Conditions:

1. Dates in Column A should be within the current work week.

2. The Items in Colum B should be “Gravel 10-20 mm Beige”.

Sum Range:

It’s the Column C containing the number of trips.

Note: If you only have the condition 1 above, then I recommend you to use the SUMIF based solution, which is more clean and elegant. Here is that tutorial – SUMIF to Sum By Current Work Week.

Since we have multiple conditions and there is no easy way to use SUMIFS, the best way to find the answer is Query.

The Query Formula to Conditionally Sum Current Work Week Range

Formula:

=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'"))

Result: 10

How this Query formula conditionally sum current work week range?

Let me explain it.

Query Syntax:

QUERY(data, query, [headers])

I am detailing my above Query formula in line with this Query syntax arguements.

1. DATA

In the Query formula, the data (range) is the below FILTER formula.

={filter({A2:A,B2:C},weeknum(A2:A)=weeknum(today()))}

This formula extracts the rows containing the current week’s date in Column A. It is the current week, not current work week. The WEEKNUM function plays the trick.

For explaining this to you, I am applying this filter in a blank range in my sheets. Here is that.

filter formula to filter current week

2. QUERY

"Select Col3 where DayofWeek(Col1)>=2 and DayofWeek(Col1)<=6 and Col2='Gravel 10-20 mm Beige'"

This is the Query. This helps me to sum Column 3 (Number of Trips) if the Column 2 items are “Gravel 10-20 mm Beige” and;

The DayofWeek in Colum 1 (date) are between the range>=2 and<=6 where 2 represents Monday and 6 represents Friday.

The DayofWeek Scalar function helps me to reduce the filter range to the current work week.

3. HEADER

It’s optional and so I haven’t included this in my formula.

Additional Tips About this Query to Sum Current Work Week Range in Google Sheets.

I want to refer my Query condition “Gravel 10-20 mm Beige” to a cell, for example to cell F10.

This is the relevant part of the Query.

Col2='Gravel 10-20 mm Beige'

Here is the modified version.

Col2='"&F10&"'

Hope this tutorial could help you to learn how to use Query to sum current work week range in Google Sheets.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.