If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DSUM - Need dynamic functionality
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 |
#2
|
|||
|
|||
DSUM - Need dynamic functionality
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 "Don" wrote in message ... 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 |
#3
|
|||
|
|||
DSUM - Need dynamic functionality
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/ex...090691033.aspx You would use the unit id in place of the "Trees" field shown in the example. John "Don" wrote in message ... 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 |
#4
|
|||
|
|||
DSUM - Need dynamic functionality
On Jun 11, 4:57*pm, "Bernie Deitrick" deitbe @ consumer dot org
wrote: 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 "Don" wrote in message ... 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- Hide quoted text - - 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. |
#5
|
|||
|
|||
DSUM - Need dynamic functionality
On Jun 11, 5:23*pm, "jaf" wrote:
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/ex...090691033.aspx You would use the unit id in place of the "Trees" field shown in the example. John "Don" wrote in ... 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- Hide quoted text - - 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 |
#6
|
|||
|
|||
DSUM - Need dynamic functionality
Hello Don,
My UDF Sfreq might be what you are looking for: http://www.sulprobil.com/html/sfreq.html Regards, Bernd |
#7
|
|||
|
|||
DSUM - Need dynamic functionality
Don,
The 'exact cells' could have GETPIVOTDATA functions... HTH, Bernie MS Excel MVP 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. |
#8
|
|||
|
|||
DSUM - Need dynamic functionality
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 "Don" wrote in message ... On Jun 11, 5:23 pm, "jaf" wrote: 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/ex...090691033.aspx You would use the unit id in place of the "Trees" field shown in the example. John "Don" wrote in ... 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- Hide quoted text - - 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 |
#9
|
|||
|
|||
DSUM - Need dynamic functionality
Don wrote...
.... 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. |
Thread Tools | |
Display Modes | |
|
|