Yogi Anand, D.Eng, P.E. Google Spreadsheet Post #517 Apr 26, 2012 www.energyefficientbuild.com.
user Hoopsnl said:
Sum per month in a specific year
Hi,
Hopefully someone can help me;
I have several dates in a spreadsheet and I want to have the monthly total per month per year, my table;
Date Total
(MM/DD-YYYY)
01/02/2011 100
01/08/2011 5
02/02/2011 6
02/08/2011 6
09/02/2011 10
01/02/2012 12
01/22/2012 12
04-06-2012 5
04-08-2012 5
12-10-2012 6
12-25-2012 6
Result should be:
Month Total
Jan 2011 105
Feb 2011 12
Sep 2011 10
Jan 2012 24
Apr 2012 10
Okt 2012 12
Who can help ?
----------------------------------------------------------------------------------------
following is a solution to the problem ... in Sheet1 I have shown Month numbers instead of Month names
user Hoopsnl said:
Sum per month in a specific year
Hi,
Hopefully someone can help me;
I have several dates in a spreadsheet and I want to have the monthly total per month per year, my table;
Date Total
(MM/DD-YYYY)
01/02/2011 100
01/08/2011 5
02/02/2011 6
02/08/2011 6
09/02/2011 10
01/02/2012 12
01/22/2012 12
04-06-2012 5
04-08-2012 5
12-10-2012 6
12-25-2012 6
Result should be:
Month Total
Jan 2011 105
Feb 2011 12
Sep 2011 10
Jan 2012 24
Apr 2012 10
Okt 2012 12
Who can help ?
----------------------------------------------------------------------------------------
following is a solution to the problem ... in Sheet1 I have shown Month numbers instead of Month names
Hello Yogi, i don't understand why the month(A)+1
ReplyDeleteWhy that +1 ? If you ask for month(A) the query function will not send the right month number indeed ?
Hi ABSALON:
ReplyDeleteThe reason I used month(A)+1 in the QUERY function ... because in QUERY function month numbers go from 0 to 11 rather than from 1 to 12.
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
user Hoopsnl commented:
ReplyDeleteHi Yogia,
Thanks, but the Date is in "Sheet1 A1:A500" and the amount in "Sheet1 Q1:Q500" as I wrote:
1 ) DATES; Sheet1 A1:A500
1 ) AMOUNTS; Sheet1 Q1:Q500
1 ) Result; Sheet2 A102
In your sample you have the amount in cel B... Could you be so kind to let me know what the formula should be if the amounts are in cel
"Sheet1 Q1:Q500" ?
Is there a way to auto refresh the query whe something has been changed or added ?
Regards,
Huub
If your Amount is in column Q instad of being in column B, change my formula
from:
=query(Sheet1!A:B,"select month(A)+1, year(A),sum(B) where A is not null group by month(A)+1,year(A) order by year(A) label month(A)+1 'Month number' ")
to:
=query(Sheet1a!A:Q,"select month(A)+1, year(A),sum(Q) where A is not null group by month(A)+1,year(A) order by year(A) label month(A)+1 'Month number' ")
the formula using the QUERY function auto refreshes when something has been changed or added
to the related range in Sheet1.
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com