Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSUM not unique records
I am using an advanced filter to copy a list and then using the DSUM
function to sum all the appropriate fields I would like. However, I believe there is a problem with the DSUM function as I am using it. The data looks similar to the sample data below. The probelm occurs when there are two fields similar to each other, such as "ABC123" and "ABC123D" which appear separate on the list using advanced filter, but the after checking the data, the amounts for "ABC123D" are included in "ABC123". If there is a way for the DSUM function, or another function to read exact variables, and not so inclusive, that is what I am looking for. There are more than one occurrence of this problem, so I cannot list it out individually (i.e, "ABC123" & "ABC123D" also "DEF123" & "DEF123S") since there are over a dozen cases in my file. If there is a better way to do this without using a pivot table, please let me know, any help is greatly appreciated. Rep Sales Comm ABC123 500 100 DEF123 400 50 HIJABC 480 80 ABC123D 100 20 DEF123S 200 50 GEF456 800 120 ABC123 300 75 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSUM not unique records
There are two changes you have to make to get DSUM to work. First, change
your criteria from the literal "ABC123" to a formula like =A2="ABC123" where A2 is the first data cell in the Rep column of your database. Second, clear the "Rep" header in the first cell of your criteria range. This cell should be empty (but included in the Criteria range). wrote in message ... I am using an advanced filter to copy a list and then using the DSUM function to sum all the appropriate fields I would like. However, I believe there is a problem with the DSUM function as I am using it. The data looks similar to the sample data below. The probelm occurs when there are two fields similar to each other, such as "ABC123" and "ABC123D" which appear separate on the list using advanced filter, but the after checking the data, the amounts for "ABC123D" are included in "ABC123". If there is a way for the DSUM function, or another function to read exact variables, and not so inclusive, that is what I am looking for. There are more than one occurrence of this problem, so I cannot list it out individually (i.e, "ABC123" & "ABC123D" also "DEF123" & "DEF123S") since there are over a dozen cases in my file. If there is a better way to do this without using a pivot table, please let me know, any help is greatly appreciated. Rep Sales Comm ABC123 500 100 DEF123 400 50 HIJABC 480 80 ABC123D 100 20 DEF123S 200 50 GEF456 800 120 ABC123 300 75 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSUM not unique records
On Aug 1, 12:59*pm, "Jim Rech" wrote:
There are two changes you have to make to get DSUM to work. First, change your criteria from the literal "ABC123" to a formula like =A2="ABC123" where A2 is the first data cell in the Rep column of your database. *Second, clear the "Rep" header in the first cell of your criteria range. *This cell should be empty (but included in the Criteria range). wrote in message ... I am using an advanced filter to copy a list and then using the DSUM function to sum all the appropriate fields I would like. *However, I believe there is a problem with the DSUM function as I am using it. The data looks similar to the sample data below. *The probelm occurs when there are two fields similar to each other, such as "ABC123" and "ABC123D" which appear separate on the list using advanced filter, but the after checking the data, the amounts for "ABC123D" are included in "ABC123". If there is a way for the DSUM function, or another function to read exact variables, and not so inclusive, that is what I am looking for. *There are more than one occurrence of this problem, so I cannot list it out individually (i.e, "ABC123" & "ABC123D" also "DEF123" & "DEF123S") since there are over a dozen cases in my file. If there is a better way to do this without using a pivot table, please let me know, any help is greatly appreciated. Rep Sales Comm ABC123 * * * * * * 500 100 DEF123 * * * * * * 400 50 HIJABC * * * * * *480 80 ABC123D * * * * * *100 20 DEF123S * * * * * *200 50 GEF456 * * * * * *800 120 ABC123 * * * * * *300 75- Hide quoted text - - Show quoted text - That did not seem to solve the problem. The array is in cells A1:C8, and B13= "Sales" and C13 = "Comm". My criteria range is in cells A10:A11, (A10 = "Rep", and A11 is blank). The Rep names are listed in cells A15 to A20. In cell B14the formula is =DSUM(A1:C8,"Sales",A10:A11) which gives the total, then I highlight A14:C20 and create a table with the column criteria as A11. This gives a summary table, but will double count entries, such as ABC123 has sales amount that corresponds to ABC123 and also ABC123D (sales should equal 800 and not 900, as it currently shows). Thanks for the help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSUM not unique records
On Aug 1, 9:42*am, wrote:
I am using an advanced filter to copy a list and then using the DSUM function to sum all the appropriate fields I would like. *However, I believe there is a problem with the DSUM function as I am using it. The data looks similar to the sample data below. *The probelm occurs when there are two fields similar to each other, such as "ABC123" and "ABC123D" which appear separate on the list using advanced filter, but the after checking the data, the amounts for "ABC123D" are included in "ABC123". If there is a way for the DSUM function, or another function to read exact variables, and not so inclusive, that is what I am looking for. *There are more than one occurrence of this problem, so I cannot list it out individually (i.e, "ABC123" & "ABC123D" also "DEF123" & "DEF123S") since there are over a dozen cases in my file. If there is a better way to do this without using a pivot table, please let me know, any help is greatly appreciated. Rep * * * * * * * * * * Sales * * * * * Comm ABC123 * * * * * * * * * * *500 * * * * 100 DEF123 * * * * * * * * * * *400 * * * * 50 HIJABC * * * * * * * * * * 480 * * * * *80 ABC123D * * * * * * * * * *100 * * * * *20 DEF123S * * * * * * * * * *200 * * * * *50 GEF456 * * * * * * * * * * 800 * * * * *120 ABC123 * * * * * * * * * * 300 * * * * *75 I wonder if this might?? be simplified by simply using a filter and then using the SUBTOTAL(109 function instead?? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSUM not unique records
On Aug 1, 11:59*am, "Jim Rech" wrote:
There are two changes you have to make to get DSUM to work. First, change your criteria from the literal "ABC123" to a formula like =A2="ABC123" where A2 is the first data cell in the Rep column of your database. *Second, clear the "Rep" header in the first cell of your criteria range. *This cell should be empty (but included in the Criteria range). wrote in message ... I am using an advanced filter to copy a list and then using the DSUM function to sum all the appropriate fields I would like. *However, I believe there is a problem with the DSUM function as I am using it. The data looks similar to the sample data below. *The probelm occurs when there are two fields similar to each other, such as "ABC123" and "ABC123D" which appear separate on the list using advanced filter, but the after checking the data, the amounts for "ABC123D" are included in "ABC123". If there is a way for the DSUM function, or another function to read exact variables, and not so inclusive, that is what I am looking for. *There are more than one occurrence of this problem, so I cannot list it out individually (i.e, "ABC123" & "ABC123D" also "DEF123" & "DEF123S") since there are over a dozen cases in my file. If there is a better way to do this without using a pivot table, please let me know, any help is greatly appreciated. Rep Sales Comm ABC123 * * * * * * 500 100 DEF123 * * * * * * 400 50 HIJABC * * * * * *480 80 ABC123D * * * * * *100 20 DEF123S * * * * * *200 50 GEF456 * * * * * *800 120 ABC123 * * * * * *300 75 Could you just use =SUMPRODUCT((( |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
DSUM not unique records
I see now that you're doing a What-if table along with DSUMs. The technique
I suggested will not work in conjunction with a What-if, just standalone. Might I suggest doing away with the What-if and just use SUMPRODUCT? This formula in cell B15 in your example, copied down and across to C20 works for me: =SUMPRODUCT(B$2:B$8,($A$2:$A$8=$A15)*1) wrote in message ... On Aug 1, 12:59 pm, "Jim Rech" wrote: There are two changes you have to make to get DSUM to work. First, change your criteria from the literal "ABC123" to a formula like =A2="ABC123" where A2 is the first data cell in the Rep column of your database. Second, clear the "Rep" header in the first cell of your criteria range. This cell should be empty (but included in the Criteria range). wrote in message ... I am using an advanced filter to copy a list and then using the DSUM function to sum all the appropriate fields I would like. However, I believe there is a problem with the DSUM function as I am using it. The data looks similar to the sample data below. The probelm occurs when there are two fields similar to each other, such as "ABC123" and "ABC123D" which appear separate on the list using advanced filter, but the after checking the data, the amounts for "ABC123D" are included in "ABC123". If there is a way for the DSUM function, or another function to read exact variables, and not so inclusive, that is what I am looking for. There are more than one occurrence of this problem, so I cannot list it out individually (i.e, "ABC123" & "ABC123D" also "DEF123" & "DEF123S") since there are over a dozen cases in my file. If there is a better way to do this without using a pivot table, please let me know, any help is greatly appreciated. Rep Sales Comm ABC123 500 100 DEF123 400 50 HIJABC 480 80 ABC123D 100 20 DEF123S 200 50 GEF456 800 120 ABC123 300 75- Hide quoted text - - Show quoted text - That did not seem to solve the problem. The array is in cells A1:C8, and B13= "Sales" and C13 = "Comm". My criteria range is in cells A10:A11, (A10 = "Rep", and A11 is blank). The Rep names are listed in cells A15 to A20. In cell B14the formula is =DSUM(A1:C8,"Sales",A10:A11) which gives the total, then I highlight A14:C20 and create a table with the column criteria as A11. This gives a summary table, but will double count entries, such as ABC123 has sales amount that corresponds to ABC123 and also ABC123D (sales should equal 800 and not 900, as it currently shows). Thanks for the help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unique Values, not Unique Records | Excel Discussion (Misc queries) | |||
Unique Records | Excel Worksheet Functions | |||
Filer for unique records and return all column data for unique rec | Excel Discussion (Misc queries) | |||
How to get unique records? | Excel Discussion (Misc queries) | |||
unique filter results in some non-unique records. | Excel Discussion (Misc queries) |