Google Spreadsheet Group By Query based on Date condition

Question:

( 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.

Solution:

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:

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