Thank you both for responding. Rob, the Data Subtotals function goes part
way but Pete's SUMPRODUCT does exactly what I need. I would never have found
it!

It opens the door to manipulating other bits of data from the same sheet on
a similar basis. Given some data entry validation it should work like a
charm.

I'm really grateful for your help

Regards

Pete

"Pete_UK" wrote:

As you have suggested, use Sheet1 to enter the date (A), name (B) and
amount (C) in 3 columns. Then in Sheet2 you can set up a table showing
the list of collector's names in A2:A27, and the months in B1:M1. Then
in B2 you can have a SUMPRODUCT formula to give the amount against
each name for each month. The formula would be something like:

=SUMPRODUCT((Sheet1!$B$2:$B$500=$A2)*(TEXT(Sheet1! $A$2:$A$500,"mmm")=B
$1)*(Sheet1!$C$2:$C$500))

which assumes you have just a 3-letter month in B1:M1, like Jan, Feb,
Mar etc. Copy the formula across and down as required.

You could use a pivot table to do the same.

Hope this helps.

Pete

On Jan 26, 3:07 pm, Parish Pete
wrote:
I trying to sort out a small spreadsheet for a charities collections and want
to keep as simple as possible as the users are novices. They are using Excel
2000.

I set up a sheet that worked, but of necessity contained 26 columns ( one
for each collectors takings) spread over 200 rows (Theoretically 366 max)..
In practical terms it wasn't user friendly.

In essence it would be better to need only to enter a date, a name and a sum
of money in 3 columns and have totals calculated in secondary sheets.

My guess is that in order to get meaningful monthly totals against each of
the 26 collectors will require some VBA code to pull that data into separate
sheet.

Can anyone guide me on data manipulation. I need to identify all instances
of each of the names and total each name's takings for each calender month.

thanks

Pete