Google Spreadsheet Post #899
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Nov 29, 2012
user Nathan Gibson said:(http://productforums.google.com/forum/?zx=fgswwk4cu5xw#!category-topic/docs/spreadsheets/Oa3NjbWNVIY)
get the sum of a cell depending on the DATE value of another Cell
I have spreadsheet1 with column C listing a date in MM/D/YYYY format and Column G listing a dollar Amount.
I need a formula to parse speadsheet1 and find all dates in comlumn C for January and add Column G and post it in spreadsheet 2. And so on for each month
Spreadsheet 1 <----What I got
C G
1/1/12 $5.00
1/5/12 $5.00
2/5/12 $10.00
2/12/12 $7.00
3/15/12 $2.00
5/22/12 $9.00
6/16/12 $6.00
Spreadsheet 2: <-----What I need
A B
January $10.00
February $17.00
March $2.00
April $0.00
May $9.00
June $6.00
I am not a spreadsheet master but I was working down this path but haven't really got anywhere: =ArrayFormula(sum( (month(C2:C)=9)*E2:E ))
Any help would be appreciated.
-------------------------------------------------------------------------------
I have a hunch you want to group in Sheet2 sum of values in column D by month of dates in column C of Sheet1 -- for this ... for this I have presented a solution in the following
Thanks for the quick response, I am trying to make this your example work, but Im not sure we are on the same page.
ReplyDeleteI got this example to provide what I need, but it doesnt work if there are empty cells in either column
=ArrayFormula(SUM(IF(MONTH('Sheet1'!C2:C7)=11,'Sheet1'!G2:G7,0)))
Hi Nathan:
ReplyDeleteSorry about that ... I have two solutions for months by numbers and months by names. I have also included empty cells in the columns and the solution works.
Please have a look and see how this works out for you.
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
Hi,
ReplyDeleteAdapting the above query formula for my data in Col C5:E in sheet 5, i am able to attain following result. Column C (C5 and below) has date in dd/mm/yyyy format and Column E has Numeric Value
=query('Sheet5'!C5:E,"select month(C)+1,sum(E) where C is not null group by month(C)+1 label month(C)+1 'Month', sum(E) 'Sum' ")
Result
Month Sum
4 100
What I am trying to achieve is as follows; In Sheet 1 Cell C7 has date in dd/mm/yyyy format and based on that date i want to get total sum of month corresponding to this date.
Let say my date is 15/4/2013 in Sheet 1 then I should get total sum of month of April in Cell C11.
Hope this example makes easier to follow.
https://docs.google.com/spreadsheet/ccc?key=0AnVO1W9RSxhydEdvMUpmaURvV0dPN1piZk5tNDZFX1E#gid=0
Thanks
NK