I have a large table with approx. 1500 rows and 34 columns. It
compiles and summarizes payroll hours, by day of the week, type of
hour, and location.

Each row contains a different unit identifier code.


Data is stored in 7 separate daily payroll files, one day on each tab
of the workbook. We have complex tab names such as SA, SU, MO, TU,
WE, TH, FR for these files. As you see, real complex.


Anyway, the dsum formula is pretty straight forward as shown below.


=DSUM(SU!$A$1:$J$10000,"OT",SU!$M$1:$O$2)


Here comes the problem. I need a way to make the DSUM formula
dynamic
or the criteria cells to be dynamic so the unit identifier code is
brought into play. The daily payroll data files are between 5,000
and
50,000 lines and the same unit identifier will show up many times.


As the formula is written above, cell O2 is where the unit identifier
is shown. Although I realize I could just repeat M1:O2 1500 times
simply changing the unit identifier each time, it seems as though
there should be an easier way.


I am currently using SUMPRODUCT to get our data but it is very slow
to
compile and very processor intensive. DSUM seems to work much
quicker
and appears to be as accurate so it looks like an alternative.


Your assistance is greatly appreciated.


Don