Thursday, November 17, 2011

yogi_Compute Sum By WeekNumber For A Given Range Of Dates and Amounts


Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user gassiep said:
get a weeknumber of a date for grouping with query
I want to use a spreadsheet as a source for the query function. =query(sheet!A:B ; "Select ???? , sum(B) group by ????") 
I have this data:
A            B 

1-1-2011    12
1-30-2011   34
2-12-2011   23
With the query function I want to sum coulm B by weeknumbers of the date in column A. 
I also tried creating an extra column C which uses the text function for the weeknumber, but then the query gives a error saying column C doesn't exist in the source :(
How can I do this?
-----------------------------------------------------------------

following is one solution to the problem wherein I create a virtual column of weeknumbers and then use the QUERY function to group by weeknumber and sum up the corresponding amounts: