ArrayFormula Output Ordering

Question

I am trying to take the output of the ArrayFormula in the top answer of this previous question, where the output is days of the week, and I want to output them in the proper order (Sunday, Monday, ..., Saturday).

Currently, when I use this formula I get the order Friday, Monday, Saturday, Sunday, Thursday, Tuesday, Wednesday. I have tried using the "order by" clause listing the days of the week in order order by("Sunday", "Monday", ..., "Saturday" without success.

My formula right now is =QUERY({H:H,H:H},"select Col1, count(Col2) where Col1 != '' group by Col1 label count(Col2) 'Number of Calls'",1). A sanitized version of the data I am attempting to use is here. This data is over the course of one year. It has also been edited to include the two solutions I have so far for reference of future viewers.

Is it currently possible to do the ordering of the days within the Query? If not, what is the best way of going about getting the correct order.

What does your data look like? Is it just one week? Does it span multiple weeks,months, and years? What is the formula you are trying. It probably can be done, but more information is needed if you want an answer. — Ed Nelson, Dec 14 '15 at 22:44
@EdNelson I have edited it. I have provided a data sample, as well as the formula in its current form. — Thrakerzad, Dec 15 '15 at 20:08
@pnuts I don't necessarily have a solution, but if a nested query could be written that writes the day of the week in column H as it corresponds to the dates in column A, then writes the months once each into column I based on the order established in column H with an order by H, then finally writes the count number in column J. Complicated, but I think that is the only way to do it without a hack, while staying within the constraints of using a query. — Thrakerzad, Dec 17 '15 at 14:33
@pnuts As far as I can tell, you are correct. I've sent Google some feedback and hopefully they implement the kind of sorting I am looking for. It looks like the answer by Ed is the best possible choice as things stand right now. — Thrakerzad, Dec 17 '15 at 14:47

score 0 · Accepted Answer · edited Dec 17 '15 at 14:51

I can't edit your sheet, but this is what I suggest. I don't understand why you have columns D-G, but I will assume you have a reason and work with what you have. First, change H2 to =weekday(F2) and copy it down to H66 (last row). This will return the number of the day of the week (Sunday=1, Monday=2...Saturday=7). Then in I2-I8 fill in the days of the week in the order Sunday through Saturday. In J2 put:

 =COUNTIF($H$2:H,"=1")

for Sunday. In J3 put:

=COUNTIF($H$2:H,"=2")

for Monday. And so on through Saturday. That should do it.

ArrayFormula Output Ordering

1 Answers1