Excel 2002 : How to sum by month and year ?

[Guest]

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,D28),""), but it does not work because all the
cells in column B are date values.


Thanks

Low

 

[Guest]

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.

 

[Don Guillett]

One way, withOUT any helper columns
=SUMPRODUCT((YEAR(D2325)=2001)*(MONTH(D2325)=2)*E23:E25)

 

[Roger Govier]

Hi

The easiest way is to create a Pivot Table.
Mark your range of data.
Data>Pivot Table>Finish
Drag Date to the Row area of the Pivot Table skeleton
Drag Amount to the Data area
Right click on Date>Group>Group by>select Year and Month>OK

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/flashfiles/pivot1.html

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