# How to Count Orders per Week in Google Sheets

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

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 `ARRAYFORMULA` function.

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

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.
2. 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.
3. 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.

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

### Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive early access to new content.

## How to Use FLATTEN Function in Google Sheets

The FLATTEN function in Google Sheets is useful for creating a column that includes the numbers, characters, or…

## How to Use XOR Function in Google Sheets

The XOR function in Google Sheets lets you check if the data you input in the spreadsheet meets…

## How to Use ISEMAIL Function in Google Sheets

The ISEMAIL function in Google Sheets is used to determine if a value is a valid e-mail address.…

## How to Use DATEDIF Function in Google Sheets

The DATEDIF function in Google Sheets is useful if you want to know how far two different given…