MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Dave H - Help with Database functions please

Posted by Zif on April 18, 2001 7:59 AM


You responded to my eariler posting about alternatives to using array formulas and suggested that I use Database functiosn instead.

I'm having some trouble in summarisign the data as I'd like.

To simplify: I have 3 columns of data (Date, Amount, Division). I need to be able to summarise the data in a table with months in Column A and Divisions in Columns C to G (5 division names).

This means that I have a total of 60 different criteria:
Criteria 1: date >= 01/01/2001, date <= 31/01/2001, division = Alpha
Criteria 2: date >= 01/01/2001, date <= 31/01/2001, division = Bravo
...and so on for each division for January, then repeat for dates in February, then March etc.

Any suggestions as to how I can simplify all these different criteria?


Posted by lenze on April 18, 2001 9:37 AM

Dave may have another way, but your problem seems ideal for a Pivot Table. Choose Data, pivot table report. Once in the wizard, drag the date field to the row area, the division to the column area, and the amount to the data area. Then group the date field by months. Should produce a table just like you described.