Count Amounts by Day of the Week in Google Sheets

Adam Steinfurth

This Google Sheets tutorial details two methods to count transactions by day of the week. We’ll explore the versatility of the QUERY function and delve into the power of Pivot Tables to create these summaries. Let’s get started!

Make a copy of this live Google Sheet to follow along. Adjust the directions accordingly when using your data.

Desired Outcome

First, let’s define our goal. We want to find totals for each day of the week from a list of transactions on multiple days. The output will be a list of each day next to its corresponding total.

The source table to be counted and the two tables showing counts
Source Detail and Two Summaries

Now, let’s start with the first solution. If you don’t want a formula, this will be the one for you.

Example 1 – Pivot Table with Date Grouping

This first example will use the built-in date grouping feature for pivot tables in Google Sheets. This feature will do most of the work for you, eliminating the need for formulas.

Pivot tables summarize large amounts of data using menus instead of formulas. See this YouTube video for a primer on pivot tables if you are unfamiliar with them.

Data range for the pivot table
Data Range

With the example sheet open, go to the Insert menu and choose Pivot table. Specify A1:C1001 as the data range.

Press OK once you have the correct data range.

Then Google Sheets will ask where you want the Pivot table.

We’ll place it on the Existing sheet in cell E2. Click Create after choosing the location.

Create pivot table dialog box
Location of Pivot Table
The pivot table editor with the COUNT option in the Summarize by drop down
Pivot Table Editor

Clicking the Create button brings up the Pivot table editor in the sidebar. In the editor, we’ll drag and drop the Manufacture Date field into the Rows and the Price field into the Values.

Change the Summarize by drop-down from SUM to COUNT. COUNT works since you’re counting a field with dates. If you were counting text, consider using COUNTA.

Then, right-click on the dates, choose Create pivot date group, and select Day of the week.

Day of the week option in the right-click menu
Day of the Week Menu Option

Now, you have a finished pivot table with the amounts counted by day of the week.

Finished pivot table showing counts by day
Day of the Week Summary

As you can see, pivot tables do much of the work without code. However, the query function is the best fit if you want to use a formula. Let’s look at that now.

Example 2 – Query Function

The query function is in a class of its own compared to traditional spreadsheet functions. It allows the spreadsheet user to write SQL commands inside the formula. We’ll use query to get daily counts, just as we did above, but with a formula. Let’s take a look at the query syntax.

Query Function Syntax

=QUERY(data, query, [headers])

  • data – Location of the data
  • query – The query statement. The query is composed of optional clauses that you must use in order.
    • select – The columns to return. If omitted, the query returns all columns.
    • where – Conditions that rows must meet
    • group by – Aggregates values
    • pivot – Aggregates values into new columns
    • order by – Sorts rows by values
    • limit – Limits the number of rows returned
    • offset – Skips a specified number of rows
    • label – Creates column labels
    • format – Formats outputs
    • options – Sets additional options 
  • [headers] – The number of header rows at the top of the data

Writing the Query

Now that we know the syntax, let’s write out the formula. We will still use A1:C1001 as the range and there’s one header row. So, we know the values for the data and headers arguments. Here is what we have so far:

=QUERY(A1:C1001, query, 1)

For the query, we want to return a column for the day of the week and a column for the counts. SQL has a scalar function called dayOfWeek and an aggregation function called count. We’ll use both of those. We’ll then use the group by clause to tell the function how to show the output, and we’ll add headers using the label clause. To polish it off, we’ll use the format clause to change the day numbers to words and the numbers to show no decimal places but have a thousands separator if needed. This is what the formula looks like now:

=QUERY(A1:C1001, "select dayOfWeek(Col2), count(Col3) group by dayOfWeek(Col2) label dayOfWeek(Col2)'Day',count(Col3)'Count' format dayOfWeek(Col2)'dddd',count(Col3)'#,##0'", 1)

The QUERY function producing counts by day of the week
Day of the Week Summary

💡Unlike the native spreadsheet COUNT function, the count inside a QUERY counts text and numbers.

The long formula creates this simple, compact table of just the data you need, sorted by day of the week!

Video Tutorial

YouTube player

Conclusion

Google Sheets has two great options for counting data by day of the week. Choose the pivot table for ease of use and the query function for the power of a formula written in SQL. Whichever technique you use, you’ll get the answer you’re looking for.

Related Tutorials