Google Spreadsheet Group By Query Summed Times

Question:

( by Kyle Sandars )

I am trying to make a spreadsheet that will auto order the range by column C and still read 1, 2, 3, 4, 5, ... in Column A.

How can I do this with Google Scripts or something?

====

I am trying to make it take the form data and add up the people that submit data (it'll be a set list of people though) and then after adding the time I want it to rank them in descending order so it will always display the person with the most time on top and the rest below in consecutive order. 

Another thing I would like to add if it is possible is the option for it to take the name assigned to their google account and find it on the spread sheet and highlight it. But that is optional.

Solution:

Have a look at the following screenshot of Sheet 'Summed Times' of my Spreadsheet:

I have the following formula in Cell A1:

=arrayformula(if(row(A:A)=1;"Rank";if(B:B="";"";row(A:A)-1)))


And I have the following formula in Cell B1:

=arrayformula( iferror( time(;; query( iferror( if({1,0}; 'Form Responses'!B:B; (hour('Form Responses'!C:C)*3600) + (minute('Form Responses'!C:C)*60 + (second('Form Responses'!C:C)) ) ) );"select Col1,sum(Col2) where Col1<>'' group by Col1 order by sum(Col2) desc label Col1 'Name',sum(Col2) 'Time Practiced' ";1)); query( iferror( if({1,0}; 'Form Responses'!B:B; (hour('Form Responses'!C:C)*3600) + (minute('Form Responses'!C:C)*60 + (second('Form Responses'!C:C)) ) ) );"select Col1,sum(Col2) where Col1<>'' group by Col1 order by sum(Col2) desc label Col1 'Name',sum(Col2) 'Time Practiced' ";1) ))


=========



And if you have some people having same time and want to assign same ranks to them, then you should use =rank function.

Have a look at the following screenshot:

Have a look at the following screenshot of Sheet "Summed Times":

As you can see in the above sheet Sally and iGoogleDrive are having same summed time and therefore they must be having same rank.

To get results like above, use the following formula in Cell A1:

=arrayformula(if(row(A:A)=1;"Rank";if(B:B="";"";rank(C:C;indirect("C2:C")))))

and put the same formula (as mentioned above) in Cell B1.

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive