Thursday, August 29, 2013

Google Spreadsheet Group By Query based on Date condition


( by Jeff Wasson )

I am using Google Forms to collect information.
It is being dumped into a response Sheets:

From the Response, I have a scorecard that I want to auto populate from the "response" tab. 
It needs to be able to automatically populate once I change the date.

SUMIFS would work in Excel, but I can't seem to figure out a clean way to do that in Sheets.
Any help would be appreciated.


Have a look at the following screenshot of sheet 'Scorecard':

And I have the following formula in Cell B3:
=query('Response'!A:G;"select B,sum(C),sum(D),sum(E),sum(F),sum(G) where B<>'' and toDate(A)= date '"&text(B2;"yyyy-MM-dd")&"' group by B label sum(C) 'Add Type',sum(D) 'Upgrade Type',sum(E) 'Option 1',sum(F) 'Option 2',sum(G) 'Accessory' ";1)

So now you have a single formula solution, and when you will update the date in cell B2, the data will update on sheet 'Scorecard'

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:
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: 


No comments:

Post a Comment