Analyzing sales data requires tracking orders made weekly, which aids inventory planning and provides insights into business growth. When your dataset contains only order timestamps or dates, you’ll need a method to extract the specific week number from each date. This guide provides a tutorial on counting orders per week in Google Sheets. We’ll explore using the WEEKNUM function and QUERY function to achieve this.
Counting Orders per Week using the QUERY function
Here’s how to count your weekly orders with the QUERY function:
Step 1: Select an empty cell and type the QUERY function. Use the formula HSTACK( A2:B, ARRAYFORMULA(WEEKNUM( A2:A, 1))) to generate a reference to your dataset with an added week number field appended at the end.
Replace A2:B with the range you wish to count and replace A2:A with the range containing the date values of each order.
Step 2: Provide a text string containing the query needed to summarize your order data.
We recommend aggregating by both year and week number. This will prevent the query from including dates from different years into the same week number.
Step 3: Adjust the query as needed by modifying or adding new clauses.
For example, you can use the WHERE function to add more restrictions to your QUERY calculation.
Counting Orders per Week using the WEEKNUM function
The WEEKNUM function calculates the week number of a specific date.
Anatomy of the WEEKNUM function
The syntax of the WEEKNUM function is:
=WEEKNUM(serial_number,[return_type])
-
=indicates the start of a function in Google Sheets. -
WEEKNUM()is the function that returns the week number of a specific date, beginning on the week containing January 1st. -
serial_numberis the date you want to find the week number for. -
return_type(optional) specifies which day the week begins on. The default is 1 (Sunday). Settingreturn_typeto 2 makes the week start on Monday, and so on.
Example
Let’s consider an example where we use the WEEKNUM function to count orders received weekly.
In the table above, we have a dataset of orders that we want to summarize to understand how many orders are made each week.
We can use the following QUERY formula to return a weekly summary:
= QUERY( HSTACK( A2:A, ARRAYFORMULA(WEEKNUM( A2:A, 1))), "SELECT YEAR(Col1), Col2, COUNT(Col1) WHERE Col1 IS NOT NULL GROUP BY YEAR(Col1), Col2 LABEL YEAR(Col1) 'Year', Col2 'Week Number', COUNT(Col1) 'Total Orders'", 0 )
Let’s break down the formula above:
The first argument of the QUERY function acts as the source data for the query. The WEEKNUM formula in column A returns the week number from 1 to 54. Since we want to return an array, we’ll wrap this function with an ARRAYFORMULA function.
We’ll then use the HSTACK function to horizontally concatenate the new array of week numbers to the original table.
Next, we’ll provide a query string that determines how we want our data to be aggregated. We’ll aggregate by both year and week number to account for dates with the same week number but on different years.
In the table above, we were able to return the total number of orders for each week covered in our dataset. For example, we now know the number of orders in each week of 2024.
We can modify our QUERY formula to include an aggregation of the quantity column as well:
=QUERY( HSTACK( A2:B, ARRAYFORMULA(WEEKNUM( A2:A, 1))), "SELECT YEAR(Col1), Col3, COUNT(Col1),SUM(Col2) WHERE Col1 IS NOT NULL GROUP BY YEAR(Col1), Col3 LABEL YEAR(Col1) 'Year', Col3 'Week Number', COUNT(Col1) 'Total Orders',SUM(Col2) 'Total Quantity'", 0 )
To add our second aggregate field, we’ll just need to add SUM(Col2) to our SELECT clause. In our QUERY output above, we now have a Total Quantity field that returns the total quantity ordered in a given week.
By using the WEEKNUM and QUERY functions in Google Sheets, you can efficiently count and analyze your orders on a weekly basis.