I'm trying to write a macro which adds summary data to a summary sheet from a
large database showing Total Sales for only those people who've made sales in
a month.

From this data ....

Name Sales Sales Date
Mary
Jack 1 05/10/2004
Jill 1 01/11/2004
Jack 1 07/11/2004
Simon
Jill 1 15/11/2004
Jill 1 21/11/2004
Jack 1 04/12/2004

I'm looking to create this output where Sales for November are being
summarised.

Name Sales
Jack 1
Jill 3

Normally I'd have a list of people on my summary sheet and use an array
formula to count the number of sales for each person.

The problem is that there are many names in the database and most of them
will never have sales and I won't know who could generate a sale until they
have.

I therefore need to find a way of extracting which names have generated a
sale as well as the number of sales.

I can get the answer by using Advanced Filter on the Date and then using
Data Subtotals but this still gives me all the data linked to the subtotals
rather than just the subtotals themselves.

Any help would be much appreciated.

Thanks

probably pedestrian this solution is

give headings viz name,sale, date
add another column for month. if the dates are in date format function
=month(cell containing date) willgive the month
prepare a criteria range
name month
jack 11
jill 11

advance filter the basic data and copy in a seaparte location
sort fiiltered data accoridng to name
use <data - subtotals for change in name
function is sum
subtotal to sales

u get what u want.
===============================

nospaminlich wrote in message
...
I'm trying to write a macro which adds summary data to a summary sheet
from a
large database showing Total Sales for only those people who've made sales
in
a month.

From this data ....

Name Sales Sales Date
Mary
Jack 1 05/10/2004
Jill 1 01/11/2004
Jack 1 07/11/2004
Simon
Jill 1 15/11/2004
Jill 1 21/11/2004
Jack 1 04/12/2004

I'm looking to create this output where Sales for November are being
summarised.

Name Sales
Jack 1
Jill 3

Normally I'd have a list of people on my summary sheet and use an array
formula to count the number of sales for each person.

The problem is that there are many names in the database and most of them
will never have sales and I won't know who could generate a sale until
they
have.

I therefore need to find a way of extracting which names have generated a
sale as well as the number of sales.

I can get the answer by using Advanced Filter on the Date and then using
Data Subtotals but this still gives me all the data linked to the
subtotals
rather than just the subtotals themselves.

Any help would be much appreciated.

Thanks