1

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

1 Answer 1

1

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:

13
  • Could the formula show a column that counts How many Sundays, How Many Mondays, etc ?
    – rosa
    Sep 30, 2020 at 2:55
  • "Could the formula show a column that counts How many Sundays, How Many Mondays, etc ?". No problem. Please check the updated answer. Sep 30, 2020 at 3:43
  • That does not work as Count(J) now counts the number of multiple rows on same Monday, same Tuesday. The needed count is number of unique Mondays, Tuesdays
    – rosa
    Sep 30, 2020 at 12:19
  • Perhaps the issue is also that days are formatted (my error) as 01/01/20 .. 09/29/20
    – rosa
    Sep 30, 2020 at 12:41
  • Sorry, I do not quite understand what you mean. Please read how to and share a test sheet so as you can be easier helped. Sep 30, 2020 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.