Grouping totals together by month per customer

[Guest]

First an example of the data I am working with:

Account# Customer Date Invoice Totals

235 Joe Jan 07 $100.00
235 Joe Jan 07 $150.00
824 Sally Jan 07 $350.00
235 Joe Feb 07 $75.00
824 Sally Mar 07 $200.00
824 Sally Mar 07 $100.00
235 Joe Mar 07 $150.00


I am need a formula that will add the Invoice totals for each row where the
Account# is equal and group together by month. The criteria will be from a
different worksheet within the same workbook so the end result would
hopefully look something like this:

Account# Customer Date Invoice Totals

235 Joe Jan 07 $250.00
Feb 07 $75.00
Mar 07 $150.00
824 Sally Jan 07 $350.00
Mar 07 $250.00

I hope this is possible and appreciate any help that will get me going in
the right direction.

Thanks ahead of time,

Mike Koop

 

[JE McGimpsey]

Best bet is a Pivot Table:


http://peltiertech.com/Excel/Pivots/pivotstart.htm

 

[Guest]

Your best bet is pivot tables. Place your cursor in the midlle of your data
set somewhere and select Data|Pivot Tables -> A wizard will pop up but you
can probably just hit finish and it will make all of the right guesses for
you. Now from the field list drag the invoice totals into the middle. Drag
the dates to the left column. Right click on the dates and select Goup. group
by month and year. Drag the Account number and Customer to the left columns
and you are done. You can add auto formats and such if you want.

 

[Guest]

First of all thank you, that is exactly what I was looking for.

Now I need to add more info to this chart, this is the some of the data that
the pivot table returned:
212300 2006 Jan 183.56
Jun 2222.32
Jul 1835.58
Aug 1835.58
2007 Jan 1835.58
Feb 1835.58
Mar 1835.58
Apr 1835.58
May 1835.58
Jun 1835.58
Jul 1835.58
Aug 1835.58
212300 Total 20761.68

Is there some way to show a total by year instead of both years together?
and is there a way to show the difference in what was spent in 2006 compared
to 2007?

Thanx

 

[Guest]

Grab the year field and drag it to the top row. That will give you a side by
side comparison and segregate the totals.

 

[Guest]

A couple of possible solutions to the difference question. One would be a
calculated field. The other is to change the aggregation from Normal to
difference from. Sorry I can not elaborate more but I am about to go into a
meeting (pesky job keeps getting in the way). Here is an excelent resource on
pivot tables...

http://www.contextures.com/tiptech.html

 

[Guest]

Thank you so much for your help, I was able to just add a calculation column
and it is just what I was looking for.

Thanks again.