Sum Daily Amounts to Monthly

[DeanR]

Here is the data. Below the data is what I want to do.


Date......... Amount

01/01/2009 125
01/02/2009 283
01/03/2009 632
etc.
12/31/2009 396


I want to calculate the totals for each month at the bottom of the data.

01/2009 xxx Total
02/2009 xxx Total
etc.
12/2009 xxx Total


I have tried the SUMIF function, but it either does not work or I have the wrong syntax.

=SUMIF(A1:A365,AND(">=1/1/2009","<=1/31/2009"),B1:B365)

It would be better for the criteria to be a reference to the month and year in column A instead of AND(">=1/1/2009","<=1/31/2009").


Thanks for your help and expertise.

Dean

 

[sgfifield]

Have you tried taking the "s out within the AND() part?

 

[DeanR]

Just tried taking the quotation marks out of the formula and it returns an error.

 

[DeanR]

I tried the following SUMIF function and it did not work. That is, the result is zero.

=SUMIF(A1:A365,AND(">="&DATE(YEAR(A367),MONTH(A367),1),"<="&DATE(YEAR(A367),MONTH(A367),31)),B1:B365)


However, if I take the AND out of the formula and SUMIF is based on date greater than 1/1/2009 the formula works. It is not the result I want, but the formula works. I must not have the AND syntax and/or logic correct.

=SUMIF(A1:A365,">="&DATE(YEAR(A367),MONTH(A367),1),B1:B365)


Dean

 

[DeanR]

I read in an Excel book that you can not use the AND function in an array formula.

I assume that the SUMIF function is a type of array formula.


-Dean

 

[dscg]

Sumproduct will work in this case:

=SUMPRODUCT((MONTH($A$1:$A$365)=MONTH(A367))*$B$1:$B$365)

 

[DeanR]

Thanks for suggesting SUMPRODUCT function as a solution. Now I will need to stretch my brain to understand how this function works.

I will test to see if this function will also work for selecting month and year as a summing criteria.

-Dean

 

[duke666]

Thanks for suggesting SUMPRODUCT function as a solution. Now I will need to stretch my brain to understand how this function works.

I will test to see if this function will also work for selecting month and year as a summing criteria.

-Dean

Hi DeanR
did you have any success with month&year as sum criteria?
I would need something like that...all help needed.

BR Gasper

 

[Beate Schmitz]

Pivottable grouped by years and months

Hello,

why don't you use PivotTable grouped by years and months?:

Excel Workbook
	A	B	C	D	E	F
1	Date	Value	*	Summe von Value	*	*
2	02.09.2009	230,90	*	Jahre	Date	Ergebnis
3	20.05.2009	390,58	*	2009	Jan	2.403,42
4	29.12.2009	776,89	*	*	Feb	3.755,13
5	20.01.2009	874,82	*	*	Mrz	2.376,12
6	13.05.2009	807,52	*	*	Apr	2.831,88
7	26.10.2009	687,34	*	*	Mai	8.438,01
8	27.03.2009	265,47	*	*	Jun	3.961,45
9	24.11.2009	562,24	*	*	Jul	4.439,21
10	14.07.2009	12,49	*	*	Aug	4.419,18
11	03.11.2009	660,86	*	*	Sep	4.064,66
12	19.09.2009	937,04	*	*	Okt	1.626,54
13	05.10.2009	368,57	*	*	Nov	4.698,98
14	19.10.2009	91,09	*	*	Dez	3.164,05
15	26.05.2009	328,21	*	*	*	*
16	23.02.2009	879,75	*	*	*	*
17	22.03.2009	974,45	*	*	*	*
18	14.11.2009	659,31	*	*	*	*
19	03.06.2009	983,36	*	*	*	*
20	09.11.2009	211,91	*	*	*	*
Sheet

I'm using german excel, that's why dates and values of my example have the wrong format.

 

[DeanR]

I have not had a chance to test if MONTH and YEAR will work with SUMPRODUCT function.

Pivot Table is probably a solution. However, I would prefer a method that displays the monthly sums below my existing data that is relatively easy to accomplish, easy for someone to understand the displayed results, and easy for another user to follow the methodology so that they can update the spreadsheet.

-Dean