DSUM - Need dynamic functionality

[Don]

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

 

[Bernie Deitrick]

Don,

Try using a pivot table. Select all your data, then use Data / Pivot table
..... and click finish. Then drag buttons onto the row and data fields -
hard to give advice beyond that, given the lack of a description of what you
want, but Pivot tables will summarize data based on unique values or
combination of values in the columns that are dragged to the row area.

HTH,
Bernie
MS Excel MVP

 

[jaf]

Hi Don,
The criteria field can use as many rows as you have criteria's. (it's a range)

So changing =DSUM(SU!$A$1:$J$10000,"OT",SU!$M$1:$O$2)
to =DSUM(SU!$A$1:$J$10000,"OT",SU!$M$1:$O$ #of unit id's +1) and fill down.
And filling in the data in column below row 2 with your additional criteria will work.

See this MS example where "apple" & "pear" trees are used as multiple criteria.
http://office.microsoft.com/en-us/excel/HP052090691033.aspx
You would use the unit id in place of the "Trees" field shown in the example.


John

 

[Don]

Don,

Try using a pivot table.  Select all your data, then use  Data / Pivot table
.... and click finish.  Then drag buttons onto the row and data fields -
hard to give advice beyond that, given the lack of a description of what you
want, but Pivot tables will summarize data based on unique values or
combination of values in the columns that are dragged to the row area.

HTH,
Bernie
MS Excel MVP













- Show quoted text -

Bermie,

Thanks. I was going to use a pivot table but I am contrained. The
figures have to plug into a stagnant report shell that is integrated
into other applications with hard codes to exact cell references so
unfortunately the flexible nature of a pivot table does not work.

 

[Don]

Hi Don,
The criteria field can use as many rows as you have criteria's. (it's a range)

So changing =DSUM(SU!$A$1:$J$10000,"OT",SU!$M$1:$O$2)
to =DSUM(SU!$A$1:$J$10000,"OT",SU!$M$1:$O$ #of unit id's +1) and fill down.
And filling in the data in column below row 2 with your additional criteria will work.

See this MS example where "apple" & "pear" trees are used as multiple criteria.http://office.microsoft.com/en-us/excel/HP052090691033.aspx
You would use the unit id in place of the "Trees" field shown in the example.

John












- Show quoted text -

John,

Thnaks but if I place all of the criteria in a vertical column, what I
end up with is a total of all fiures that match any of the criteria.
If unit 1 has 5 hours and unit 2 has 3 hours, I get 8 in every line.
What I need it 5 in the line corresponding to unit 1 and the 3 in the
line corresponding to unit 2.

Don

 

[Bernd P]

Hello Don,

My UDF Sfreq might be what you are looking for:
http://www.sulprobil.com/html/sfreq.html

Regards,
Bernd

 

[Bernie Deitrick]

Don,

The 'exact cells' could have GETPIVOTDATA functions...

HTH,
Bernie
MS Excel MVP

 

[jaf]

Hi Don,
I see that won't work for you.
You can use a different criteria range for each unit id or change the ="=Apple" field many times to get different results.
If you have 1500 unit id's a macro to do the editing, copy & paste the results into another range would work.
I don't know if it would be faster than the sumproduct method.


John

Hi Don,
The criteria field can use as many rows as you have criteria's. (it's a range)

So changing =DSUM(SU!$A$1:$J$10000,"OT",SU!$M$1:$O$2)
to =DSUM(SU!$A$1:$J$10000,"OT",SU!$M$1:$O$ #of unit id's +1) and fill down.
And filling in the data in column below row 2 with your additional criteria will work.

See this MS example where "apple" & "pear" trees are used as multiple
criteria.http://office.microsoft.com/en-us/excel/HP052090691033.aspx
You would use the unit id in place of the "Trees" field shown in the example.

John












- Show quoted text -

John,

Thnaks but if I place all of the criteria in a vertical column, what I
end up with is a total of all fiures that match any of the criteria.
If unit 1 has 5 hours and unit 2 has 3 hours, I get 8 in every line.
What I need it 5 in the line corresponding to unit 1 and the 3 in the
line corresponding to unit 2.

Don

 

[Harlan Grove]

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

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

....

Straightforward but inflexible, at least in Excel's implementation.

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.

....

Since you don't want to use SUMPRODUCT, your only other option is to
use data tables. You'll populate a result table somewhere else in the
workbook with identifiers in the first column starting in cell A2 and
your DSUM formula in cell B1. Select A1:B1500 (or how ever many rows
as needed, but both columns), run Data > Table, and select SU!O2 as
your column input cell.

This assumes SU!M2 and SU!N2 are fixed and common to all identifiers.
If not, you'd need a table with 3 columns holding your M2, N2 and O2
values for the criteria range, then use sequential integers in the
first column of your data table range. You'd need to use INDEX
formulas to pull criteria values into your criteria range from the
table of criteria values and use another cell, say X99, as the row
index for these INDEX calls. Then run the Data > Table command, but
set cell X99 as the column input cell.

In Excel (as opposed to Lotus 123), DSUM and related functions only
make sense in the context of data tables.