Excel 2002 : How to sum by month and year ?
 

Dear Sir,

I need to sum invoices according to Month and Year as illustrated below:

May I know what formula I must input at cell E2 and copy down to get the
answers :


A B C D E
1 Date Mth / Yr Invoice Amount S. Total
2 21/02/2001 Feb-01 IV8047 200
3 08/02/2001 Feb-01 IV8048 350
4 28/02/2001 Feb-01 IV8049 100 650
5 01/03/2001 Mar-01 IV8050 500
6 05/03/2001 Mar-01 IV8051 660 1100
7 02/02/2002 Feb-02 IV8054 220
8 20/02/2002 Feb-02 IV8063 120 340
I try converting date to Month / Year format in column B and using formula
=IF(B2<B3,SUMIF(B2:B8,B2,D2:D8),""), but it does not work because all the
cells in column B are date values.


Thanks

Low




--
A36B58K641

Excel 2002 : How to sum by month and year ?
 

As you realized, the format of the cells in column B isn't helping because
the underlying values still differ. I'd try something like:
=if(and(year(b2)=year(b3),month(b2)=month(b3)),"", d2) in cell e2
=if(and(year(b2)=year(b3),month(b2)=month(b3)),"", sum(d$2:d3)-sum(e$2:e2))
in cell e3, then copy that formula down to subsequent rows. The assumption
(implicit in your approach as well, so I figure it's valid) is that all the
invoices from a particular month are grouped together.


"Mr. Low" wrote:

Dear Sir,

I need to sum invoices according to Month and Year as illustrated below:

May I know what formula I must input at cell E2 and copy down to get the
answers :


A B C D E
1 Date Mth / Yr Invoice Amount S. Total
2 21/02/2001 Feb-01 IV8047 200
3 08/02/2001 Feb-01 IV8048 350
4 28/02/2001 Feb-01 IV8049 100 650
5 01/03/2001 Mar-01 IV8050 500
6 05/03/2001 Mar-01 IV8051 660 1100
7 02/02/2002 Feb-02 IV8054 220
8 20/02/2002 Feb-02 IV8063 120 340
I try converting date to Month / Year format in column B and using formula
=IF(B2<B3,SUMIF(B2:B8,B2,D2:D8),""), but it does not work because all the
cells in column B are date values.


Thanks

Low




--
A36B58K641

Excel 2002 : How to sum by month and year ?
 

One way, withOUT any helper columns
=SUMPRODUCT((YEAR(D23:D25)=2001)*(MONTH(D23:D25)=2 )*E23:E25)
--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Dear Sir,

I need to sum invoices according to Month and Year as illustrated below:

May I know what formula I must input at cell E2 and copy down to get the
answers :


A B C D E
1 Date Mth / Yr Invoice Amount S. Total
2 21/02/2001 Feb-01 IV8047 200
3 08/02/2001 Feb-01 IV8048 350
4 28/02/2001 Feb-01 IV8049 100 650
5 01/03/2001 Mar-01 IV8050 500
6 05/03/2001 Mar-01 IV8051 660 1100
7 02/02/2002 Feb-02 IV8054 220
8 20/02/2002 Feb-02 IV8063 120 340
I try converting date to Month / Year format in column B and using formula
=IF(B2<B3,SUMIF(B2:B8,B2,D2:D8),""), but it does not work because all the
cells in column B are date values.


Thanks

Low




--
A36B58K641

Excel 2002 : How to sum by month and year ?
 

Hi

The easiest way is to create a Pivot Table.
Mark your range of data.
DataPivot TableFinish
Drag Date to the Row area of the Pivot Table skeleton
Drag Amount to the Data area
Right click on DateGroupGroup byselect Year and MonthOK

For more help on Pivot Tables take a look at the following sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/f...es/pivot1.html

http://www.edferrero.com/Tutorials.aspx

--
Regards

Roger Govier


"Mr. Low" wrote in message
...
Dear Sir,

I need to sum invoices according to Month and Year as illustrated
below:

May I know what formula I must input at cell E2 and copy down to get
the
answers :


A B C D E
1 Date Mth / Yr Invoice Amount S. Total
2 21/02/2001 Feb-01 IV8047 200
3 08/02/2001 Feb-01 IV8048 350
4 28/02/2001 Feb-01 IV8049 100 650
5 01/03/2001 Mar-01 IV8050 500
6 05/03/2001 Mar-01 IV8051 660 1100
7 02/02/2002 Feb-02 IV8054 220
8 20/02/2002 Feb-02 IV8063 120 340
I try converting date to Month / Year format in column B and using
formula
=IF(B2<B3,SUMIF(B2:B8,B2,D2:D8),""), but it does not work because all
the
cells in column B are date values.


Thanks

Low




--
A36B58K641