This guide will explain how to count orders per week in Google Sheets

##### Table of Contents

In analyzing sales data, it’s important to keep track of the amount of orders made per week. Knowing this metric is helpful for inventory planning and provides valuable insight into the growth of your business.

However, if your dataset only contains a timestamp or date of each order, we’ll need a method to extract the specific week number given a date.

In this guide, we will provide a step-by-step tutorial on how to count orders per week in Google Sheets. We’ll discuss how to use the built-in `WEEKNUM`

function, which enables us to calculate the week number given a specific date.

**The Anatomy of the WEEKNUM Function**

The syntax of the `WEEKNUM`

function is as follows:

=WEEKNUM(serial_number,[return_type])

Let’s look at each argument to understand how to use the `WEEKNUM`

function.

**=**the equal sign is how we start any function in Google Sheets.**WEEKNUM()**refers to our`WEEKNUM`

function. This function returns the week number of a specific date. The week number starts counting on the week that contains January 1st.**serial_number**refers to the date you wish to find the week number of.**return_type**is an optional argument that determines which day the week begins. By default, this value is set to 1 (Sunday). If**return_type**is set to 2, then the week is calculated starting on Mondays and so on.

**A Real Example of Counting Orders per Week in Google Sheets**

Let’s explore a simple example where we can use the `WEEKNUM`

function to count the number of orders received per week.

In the table above, we have a dataset of orders that we would like to summarize. Suppose we would like 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 try to understand the formula above.

The first argument of the query function will act as the source data for our query. We’ll use the ** WEEKNUM** formula in column A to return the appropriate number from 1 to 54. Since we want to return an array, we’ll wrap this function. with an

**function.**

`ARRAYFORMULA`

We’ll then use the ** HSTACK** function to horizontally concatenate the new array of week numbers to the original table.

If we try to evaluate this outside the ** QUERY** function, we’ll return a table similar to what’s shown above.

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 our table above, we were able to return the total number of orders for each week covered in our dataset. For example, we now know that we received 32 orders in the first 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.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to count orders per week in Google Sheets.

**How to Count Orders per Week in Google Sheets**

- 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. - Next, provide a text string containing the query we’ll need to summarize our 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. - Adjust the query as needed by modifying or adding new clauses.

For example, we can use the WHERE function to add more restrictions to our QUERY calculation.

These are all the steps you need to follow to count orders per week in Google Sheets.

To learn more about using Google Sheets to aggregate or summarize your data, you can read our detailed post on how to use pivot tables to summarize data in Google Sheets.

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!