Simon Thwaites,
Another option is pivot tables
for details have a look at
http://www.contextures.com/xlPivot01.html

Cecil

"Simon Thwaites" wrote in message
...
have 5000 rows in data in the following format with 6 positions, 16 weeks
and 12 activities:

Position Week Activity Hours
PD 1 Sampling 2
AD 2 PM 3
PD 3 Analysis 2
AD 4 Analysis 1
SPM 5 PM 2
SPM 2 PM 4.5
PD 3 Sampling 6
RE 2 Analysis 5.5
RE 2 PM 8.4
SPM 5 Analysis 3
SPM 5 Analysis 4
PD 3 Analysis 3
AD 2 PM 3
AD 1 Sampling 3
PD 1 Sampling 3
PD 1 PM 1
AD 2 Analysis 4
AD 2 PM 9
AD 2 Analysis 11

I need to write a macro which can work out, for example, the amount of
time spent by PDs on Sampling between
week 4 and 7.
Currently I have loads of DSums stored and it works but highly laborious.
Can I write a macro which loops through cells and for example dumps
sub-totals in cells,
for AD time spent on Sampling
PD time spent on Analysis within a particular timescale etc.etc.