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.

sample order data

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.

count orders per week in Google Sheets

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.

adjust formula to count orders per week in Google Sheets with additional WHERE clause

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 )
use QUERY to count orders per week in Google Sheets

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.
    use WEEKNUM functionReplace 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.
    adjust formula to count orders per week in Google Sheets with additional WHERE clauseWe 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.
    use QUERY to count orders per week in Google SheetsFor 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! 

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.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like