When it comes to Sort by Day of Week, in Google Sheets, we can use my Sort by Custom Order trick or the Query function.

It highly depends on the data that we are manipulating.

Didn’t get it?

I’ll explain the scenario; I mean which method to follow in which situation, better.

When to use the Custom Sort Order?

To plainly Sort a list in Google Sheets by Day of Week, you may use the custom sort order.

When to use the Query?

If you want to apply the Sort by Day of Week chronologically (not alphabetically like A-Z) after grouping data, the easiest way is to use the Query.

But the sorted data (weekday column in Query result) will be from Sunday to Saturday, not from Monday to Sunday.

If we want the sort order like Monday-Sunday, we can use the above-said custom sort with the Sumif function.

In this Google Sheets tutorial, let’s learn all these tips and tricks.

First of all, let me show you how to use custom sort to Sort by Day of Week in Google Sheets.

It won’t involve any grouping. But, we can use the Sumif function together with it to get the data grouped and sum.

Sort by Day of Week Using Custom Sort in Google Sheets

In one of my Google Sheets file, I have the below data in the range in B2:C8.

In cell E2, we can use the below SORT formula for our desired type of sorting.

=sort(B2:C,match(B2:B,{"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday";"Sunday"},0),true)

I’m showing you the result of the above formula below.

The SORT function sorts the range B2:C in the order specified in the below array.

{"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday";"Sunday"}

If we want, we can change the strings in the array from Monday-Sunday to Sunday-Saturday, to get the result in that order.

Now let’s go to the groping part. For that, we require a different set of data. Please find that below in the range A2:B.

Query to Group and Sort by Day of Week (Sunday-Saturday)

When it comes to data manipulation, mainly grouping, other than the Pivot Table, Google Sheets offers the Query function.

No doubt, using the Query, we can easily group a date column by day of the week and also sort it in the day of the week order.

The reason, Query has the dayOfWeek() scalar function.

In the above example, I’ve used the below Query formula in cell I2 (please refer to image # 1) to populate the table in the range I2:J8.

=query({A2:B},"Select dayofweek(Col1), sum(Col2) where Col1 is not null group by dayofweek(Col1) label dayofweek(Col1)'', Sum(Col2)'' format dayofweek(Col1)'dddd'")

Why Does the Formula Return Sunday-Saturday?

In a worksheet, we can use the Weekday, one of the Date functions, to get the week number. The Query alternative is dayOfWeek.

But unlike in Weekday, we can’t specify the “Type” in the dayOfWeek scalar function in Google Sheets Query.

For example, let’s find the weekday number of the date “31/01/2021”, which is Sunday. See the below Weekday formula.

=WEEKDAY("31/01/2021", 1)

It would return 1 as the type in the formula is 1. In “type 1”, the weekday number of Sunday is 1, Monday is 2, and so on.

Let’s see what happens when we change the type in the formula to 2.

=WEEKDAY("31/01/2021", 2)

The result of the above Weekday formula will be 7 because the number of Monday is 1, Tuesday is 2, and so on.

In Query, we can’t specify the “type”. So the Query formula will return the grouped data in the default Sunday-Saturday order.

Formula Explanation (Clauses Use)

I have used a total of five clauses in the above Query formula. They are as follows.

Select:

dayofweek(Col1) – To return Day of Week numbers 1-7 (1 for Sunday, 2 for Monday, … 7 for Saturday) from the dates in the first column the range.

sum(Col2) – To sum the second column (if you want you can use the Count function).

Where:

Col1 is not null – To exclude rows that contain any blank cells in the first column.

Group By:

dayofweek(Col1) – Used this scalar function since we want to group the data by day of the week.

We want to group and sort by Day of Week in chronological sort order. But, we no need to use the Order By clause in Query. Because the grouping in Query will itself arrange the sort order.

Label:

dayofweek(Col1)'' – To remove the default Column 1 label.

Sum(Col2)'' – To remove the default Column 2 label.

Format:

dayofweek(Col1)'dddd' – To format the Day of Week numbers returned by the Select and Group By clauses to the Day of Week strings.

You May Like: What is the Correct Clause Order in Google Sheets Query?

Sumif Workaround Method to Group and Sort by Day of Week (Monday-Sunday)

I know this may be the preferred method for many of you because, in this method, the Sort by Day of Week will be from Monday to Sunday.

Also, the formula is flexible. So you can customize the sort order of the weekdays in your preferred order.

Here, I’ll use one helper column. We can, later on, remove that column.

Let’s go to the steps.

1. In cell C2, insert the below Array Formula to return the Weekdays from the dates in A2:A.

=ArrayFormula(if(len(A2:A),text(A2:A,"dddd"),))

The above is an array formula. So, you must enter the formula in a blank column.

2. First, make sure that F2:F8 is blank (please refer to image # 1). In cell F2, paste the below formula to populate the weekdays from Monday-Sunday in the range F2:F8.

{"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday";"Sunday"}

3. Then use the following Sumif array formula in cell G2 to sum B2:B based on the conditions in F2:F8 find match in A2:A.

=ArrayFormula(sumif(C2:C,F2:F8,B2:B))

The above is the Sumif formula to Group and Sort by Day of Week in Google Sheets.

Additional Tips – Removing Helper Column and Flexible Weekday Sort Order

To remove the helper column range C2:C, replace C2:C in the Sumif formula with the existing formula in cell C2. See the Sumif formula below after the said change.

=ArrayFormula(sumif(if(len(A2:A),text(A2:A,"dddd"),),F2:F8,B2:B))

Then delete the formula in C2.

To change the sort by Day of Week order, alter the order of the values in the F2 formula.

That’s all. Thanks for the stay, enjoy!