I created a report that shows the sales order for a given date but I want to know how to create a group that will show sales orders that were completed in May 2008, June 2008, July 2008, etc. Once I able to group the orders for the month then I would like to sum the revenue for the sales orders that were completed in that month. Here is an example:

SalesOrder InstallComplete Revenue
640000001 5/1/08 500.00
640000002 5/10/08 1000.00
640000003 5/15/08 1500.00
640000004 5/20/08 550.00
640000008 6/1/08 200.00
640000009 6/16/08 1009.00
640000010 7/1/08 5004.00

Group all the orders for May together.
Group all the orders for June together.

Any help will be appreciated.

Hi,

Here is one way, supposing your example data is in the range A1:C8.

Step 1 - Set up a helper column:
We will use a helper column in column D.
Add the field name "Month" into D1.
In D2, place this formula

=DATE(YEAR(B2),MONTH(B2),1)

Then fill this formula down to D8.

Select cells D2 : D8, right-click | Format.
Change the format to custom: mmm-yy




Step 2 - Create A Pivot Table
Now the helper column is set up we will create a pivot table to derive the monthly totals.

Select the range A1 : D8
Data | PivotTable
Click on Next
Make sure the range A1 : D8 address is populated in the RefEdit control.
Click on Next
Choose Existing or New worksheet - whichever you need.
Click on Finish

From the pivot table field list drag "Month" into the "Drop Row Fields Here" section of the pivot table.
From the pivot table field list drag "Revenue" into the "Drop Data Items Here" section of the pivot table.

Amend the field/table settings as necessary.



Hope that helps...

or ...
use colin L option 1
but instead of a pivot table (don't get me wrong i love pivot tables)
but you could...
under the data menu - add subtotals which will group as well and add a sum total to revenue column based on the monthyear.