Google-sheets – How to show Day of Week in Order by Day of Week in Google Sheets

formulasgoogle sheetsgoogle-sheets-datesgoogle-sheets-query

I have the following formula that displays a total by Day of Week (weekday) from a date in J and amount in K

Example
J contains 1/1/2020 … until 9/29/2020
K contains numeric value 123 … 1999

=Query(ArrayFormula({{unique(text(J2:J,"DDDD"))},
 {sumif(text(J2:J,"DDDD"),
 unique(text(J2:J,"DDDD")),K2:K)}}),"Select * where Col2<0")

All the values are correct, they just are not in the order of days, ie
Sunday Monday Tuesday Wednesday … Saturday

Instead, I have the data showing like this

Friday    1,220.00
Monday    1,234.00
Tuesday   1,999.00
Thursday  1,555.00
Saturday  1,666.00
Wednesday 1,777.00

What I would want is

Monday    1,234.00
Tuesday   1,999.00
Wednesday 1,777.00
Thursday  1,555.00
Friday    1,220.00
Saturday  1,666.00

Best Answer

EDIT 2

Following OP's 2nd request

The same day, as in 1/1/2020, then another 1/1/2020 counts as 2 when it should count as 1.

enter image description here

EDIT 1

Following OP's request

Could the formula show a column that counts How many Sundays, How Many Mondays, etc ?

Just add count(J) as part of the select clause

Please use the updated formula where I also added an exclusion of Sundays and a custom format for the Totals column.

=Query(J2:K, "select dayOfWeek(J), count(J), sum(K) 
              where J is not null and dayOfWeek(J)<>1
              group by dayOfWeek(J) 
              order by  dayOfWeek(J) 
              label dayOfWeek(J) 'Day', sum(K) 'Totals', count(J) '# of days' 
              format dayOfWeek(J) 'dddd', sum(K) '$#,##0.00' ")

(Compare the two and notice the different results)

Original answer

Instead of using your formula you can also use the following query

=Query(J2:K, "select dayOfWeek(J), sum(K) 
              where J is not null 
              group by dayOfWeek(J) 
              order by  dayOfWeek(J) 
              label dayOfWeek(J) 'Day', sum(K) 'Totals' 
              format dayOfWeek(J) 'dddd' ")

enter image description here

Functions used:

Related Topic