Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using the function DSUM
Hi,
Years ago I was able to use the function DSUM to work successfully for me. Now it won't give me the expected result. Is there something that I have forgotten or failed to observe correctly? Step 1 shows my database. Step 2 shows how I defined my criteria. In Step 3, where the word DSUM is, I entered the function "=DSUM(Database,"Dividend",Criteria) " and got the correct dividend sum for company AAAA. However, nothing else was shown for the other companies. Any help here is much appreciated. 1. Database: Date Company Dividend 12/7/03 AAAA 1250.65 24/4/05 BBBB 2345.27 ........ ..... ....... ........ ..... ....... 16/2/04 ZZZZ 1469.02 ........ ..... ....... ........ ..... ....... 7/12/06 MMMM 3218.10 ........ ..... ....... ........ ..... ....... 2. Setting Criteria: 2 columns and 2 rows Company Dividend AAAA 3. Desired Result: AAAA DSUM <-- =DSUM(Database,"Dividend",Criteria) BBBB <-- Shows blank, when the sum is expected here. CCCC <-- Shows blank, when the sum is expected here. ------- <-- Shows blank, when the sum is expected here. ------- <-- Shows blank, when the sum is expected here. ZZZZ <-- Shows blank, when the sum is expected here. TIA Tom |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using the function DSUM
Your criteria is not incorrect per se but it is not necessary to include
Dividend in the criteria, to get the Dividend for these different companies your criteria could be one column 2 rows Company BBBB Nevertheless I get the correct result using both ways so I don't know why you are getting incorrect results Of course a simpler way would be to use =SUMIF(Company_Range,"AAAA",Dividend_Range) or even =SUMIF(Company_Range,H2,Dividend_Range) Where H2 is the cell with the company name you want the criteria for -- Regards, Peo Sjoblom "Tom" wrote in message .. . Hi, Years ago I was able to use the function DSUM to work successfully for me. Now it won't give me the expected result. Is there something that I have forgotten or failed to observe correctly? Step 1 shows my database. Step 2 shows how I defined my criteria. In Step 3, where the word DSUM is, I entered the function "=DSUM(Database,"Dividend",Criteria) " and got the correct dividend sum for company AAAA. However, nothing else was shown for the other companies. Any help here is much appreciated. 1. Database: Date Company Dividend 12/7/03 AAAA 1250.65 24/4/05 BBBB 2345.27 ....... ..... ....... ....... ..... ....... 16/2/04 ZZZZ 1469.02 ....... ..... ....... ....... ..... ....... 7/12/06 MMMM 3218.10 ....... ..... ....... ....... ..... ....... 2. Setting Criteria: 2 columns and 2 rows Company Dividend AAAA 3. Desired Result: AAAA DSUM <-- =DSUM(Database,"Dividend",Criteria) BBBB <-- Shows blank, when the sum is expected here. CCCC <-- Shows blank, when the sum is expected here. ------- <-- Shows blank, when the sum is expected here. ------- <-- Shows blank, when the sum is expected here. ZZZZ <-- Shows blank, when the sum is expected here. TIA Tom |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using the function DSUM
"Peo Sjoblom" wrote in message
... Your criteria is not incorrect per se but it is not necessary to include Dividend in the criteria, to get the Dividend for these different companies your criteria could be one column 2 rows You are right about the criteria point. Why I use "Dividend" is because it is the column-field-name for the defined database. I then don't have to enter the name of each individual company in the DSUM formula. But that is not where my problem lies. It is the rest of the DSUM entry below it which is something that has escaped my memory. I vaguely recall that for the rest of the cells, the formula was entered as an array or something. Am I right? If so, how is it done? Thanks for clarifying the matter. Regards, Tom Company BBBB Nevertheless I get the correct result using both ways so I don't know why you are getting incorrect results Of course a simpler way would be to use =SUMIF(Company_Range,"AAAA",Dividend_Range) or even =SUMIF(Company_Range,H2,Dividend_Range) Where H2 is the cell with the company name you want the criteria for -- Regards, Peo Sjoblom "Tom" wrote in message .. . Hi, Years ago I was able to use the function DSUM to work successfully for me. Now it won't give me the expected result. Is there something that I have forgotten or failed to observe correctly? Step 1 shows my database. Step 2 shows how I defined my criteria. In Step 3, where the word DSUM is, I entered the function "=DSUM(Database,"Dividend",Criteria) " and got the correct dividend sum for company AAAA. However, nothing else was shown for the other companies. Any help here is much appreciated. 1. Database: Date Company Dividend 12/7/03 AAAA 1250.65 24/4/05 BBBB 2345.27 ....... ..... ....... ....... ..... ....... 16/2/04 ZZZZ 1469.02 ....... ..... ....... ....... ..... ....... 7/12/06 MMMM 3218.10 ....... ..... ....... ....... ..... ....... 2. Setting Criteria: 2 columns and 2 rows Company Dividend AAAA 3. Desired Result: AAAA DSUM <-- =DSUM(Database,"Dividend",Criteria) BBBB <-- Shows blank, when the sum is expected here. CCCC <-- Shows blank, when the sum is expected here. ------- <-- Shows blank, when the sum is expected here. ------- <-- Shows blank, when the sum is expected here. ZZZZ <-- Shows blank, when the sum is expected here. TIA Tom |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using the function DSUM
There is nothing wrong with your formula, I made a dummy table using your
example and I got the result you expected. I really don't understand how you can get a blank either, a DSUM formula that wouldn't work would return zero and unless you have turned off zero vies under toolsoptionsview or using a custom format that suppress the view of zero I can't understand why you get this result. As I said I typed in some nonsense data plus the data you posted, named the table DataBase, used your criteria and tested AAAA, BBBB, CCCC and ZZZZ and I got the expected result. Are you somehow trying to get all results in one fell swoop, then you would be better off creating a range with the values you want to sum for like AAAA BBBBB CCCC ZZZZZ assume you put those values in H2:H5, then in I2 put =SUMIF(Company_Range,H2,Dividend_Range) make the ranges absolute like $A$4:$A$50 etc and just copy down the formula as long as needed -- Regards, Peo Sjoblom "Tom" wrote in message .. . "Peo Sjoblom" wrote in message ... Your criteria is not incorrect per se but it is not necessary to include Dividend in the criteria, to get the Dividend for these different companies your criteria could be one column 2 rows You are right about the criteria point. Why I use "Dividend" is because it is the column-field-name for the defined database. I then don't have to enter the name of each individual company in the DSUM formula. But that is not where my problem lies. It is the rest of the DSUM entry below it which is something that has escaped my memory. I vaguely recall that for the rest of the cells, the formula was entered as an array or something. Am I right? If so, how is it done? Thanks for clarifying the matter. Regards, Tom Company BBBB Nevertheless I get the correct result using both ways so I don't know why you are getting incorrect results Of course a simpler way would be to use =SUMIF(Company_Range,"AAAA",Dividend_Range) or even =SUMIF(Company_Range,H2,Dividend_Range) Where H2 is the cell with the company name you want the criteria for -- Regards, Peo Sjoblom "Tom" wrote in message .. . Hi, Years ago I was able to use the function DSUM to work successfully for me. Now it won't give me the expected result. Is there something that I have forgotten or failed to observe correctly? Step 1 shows my database. Step 2 shows how I defined my criteria. In Step 3, where the word DSUM is, I entered the function "=DSUM(Database,"Dividend",Criteria) " and got the correct dividend sum for company AAAA. However, nothing else was shown for the other companies. Any help here is much appreciated. 1. Database: Date Company Dividend 12/7/03 AAAA 1250.65 24/4/05 BBBB 2345.27 ....... ..... ....... ....... ..... ....... 16/2/04 ZZZZ 1469.02 ....... ..... ....... ....... ..... ....... 7/12/06 MMMM 3218.10 ....... ..... ....... ....... ..... ....... 2. Setting Criteria: 2 columns and 2 rows Company Dividend AAAA 3. Desired Result: AAAA DSUM <-- =DSUM(Database,"Dividend",Criteria) BBBB <-- Shows blank, when the sum is expected here. CCCC <-- Shows blank, when the sum is expected here. ------- <-- Shows blank, when the sum is expected here. ------- <-- Shows blank, when the sum is expected here. ZZZZ <-- Shows blank, when the sum is expected here. TIA Tom |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using the function DSUM
"Peo Sjoblom" wrote in message
... There is nothing wrong with your formula, I made a dummy table using your example and I got the result you expected. I really don't understand how you can get a blank either, a DSUM formula that wouldn't work would return zero and unless you have turned off zero vies under toolsoptionsview or using a custom format that suppress the view of zero I can't understand why you get this result. As I said I typed in some nonsense data plus the data you posted, named the table DataBase, used your criteria and tested AAAA, BBBB, CCCC and ZZZZ and I got the expected result. Are you somehow trying to get all results in one fell swoop, Yes, that was what I got before. The worksheet with an old database shows precisely that. The first cell shows the formula, =DSUM(Database,"Dividend",C869:D870) whereas every cell below it shows {=TABLE(,C870)}. Must have been entered as for an array because it won't allow me to edit it. Hence, I want to start everything all over again but have forgotten how the DSUM formula was entered down the column. Just copying it down won't work. then you would be better off creating a range with the values you want to sum for like AAAA BBBBB CCCC ZZZZZ assume you put those values in H2:H5, then in I2 put =SUMIF(Company_Range,H2,Dividend_Range) make the ranges absolute like $A$4:$A$50 etc and just copy down the formula as long as needed I have just tried your suggestion using SUMIF, replacing Dividend_Range with a column of figures for H2 and got all zeros in column I. Thought perhaps changing $A$4:$A$50 to A4:A50 for the Company_Range might work but again they all turned out to be zero. Did you actually get it to work for you? Appreciate your help. Regards, Tom "Tom" wrote in message .. . "Peo Sjoblom" wrote in message ... Your criteria is not incorrect per se but it is not necessary to include Dividend in the criteria, to get the Dividend for these different companies your criteria could be one column 2 rows You are right about the criteria point. Why I use "Dividend" is because it is the column-field-name for the defined database. I then don't have to enter the name of each individual company in the DSUM formula. But that is not where my problem lies. It is the rest of the DSUM entry below it which is something that has escaped my memory. I vaguely recall that for the rest of the cells, the formula was entered as an array or something. Am I right? If so, how is it done? Thanks for clarifying the matter. Regards, Tom Company BBBB Nevertheless I get the correct result using both ways so I don't know why you are getting incorrect results Of course a simpler way would be to use =SUMIF(Company_Range,"AAAA",Dividend_Range) or even =SUMIF(Company_Range,H2,Dividend_Range) Where H2 is the cell with the company name you want the criteria for -- Regards, Peo Sjoblom "Tom" wrote in message .. . Hi, Years ago I was able to use the function DSUM to work successfully for me. Now it won't give me the expected result. Is there something that I have forgotten or failed to observe correctly? Step 1 shows my database. Step 2 shows how I defined my criteria. In Step 3, where the word DSUM is, I entered the function "=DSUM(Database,"Dividend",Criteria) " and got the correct dividend sum for company AAAA. However, nothing else was shown for the other companies. Any help here is much appreciated. 1. Database: Date Company Dividend 12/7/03 AAAA 1250.65 24/4/05 BBBB 2345.27 ....... ..... ....... ....... ..... ....... 16/2/04 ZZZZ 1469.02 ....... ..... ....... ....... ..... ....... 7/12/06 MMMM 3218.10 ....... ..... ....... ....... ..... ....... 2. Setting Criteria: 2 columns and 2 rows Company Dividend AAAA 3. Desired Result: AAAA DSUM <-- =DSUM(Database,"Dividend",Criteria) BBBB <-- Shows blank, when the sum is expected here. CCCC <-- Shows blank, when the sum is expected here. ------- <-- Shows blank, when the sum is expected here. ------- <-- Shows blank, when the sum is expected here. ZZZZ <-- Shows blank, when the sum is expected here. TIA Tom |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using the function DSUM
Are you somehow trying to get all results in one fell swoop, Yes, that was what I got before. The worksheet with an old database shows precisely that. The first cell shows the formula, =DSUM(Database,"Dividend",C869:D870) whereas every cell below it shows {=TABLE(,C870)}. Must have been entered as for an array because it won't allow me to edit it. Hence, I want to start everything all over again but have forgotten how the DSUM formula was entered down the column. Just copying it down won't work. Table is a function that was introduced in either excel 2002 or 2003, it has been removed in 2007 I have never really used it except to test it a few times. then you would be better off creating a range with the values you want to sum for like AAAA BBBBB CCCC ZZZZZ assume you put those values in H2:H5, then in I2 put =SUMIF(Company_Range,H2,Dividend_Range) make the ranges absolute like $A$4:$A$50 etc and just copy down the formula as long as needed I have just tried your suggestion using SUMIF, replacing Dividend_Range with a column of figures for H2 and got all zeros in column I. Thought perhaps changing $A$4:$A$50 to A4:A50 for the Company_Range might work but again they all turned out to be zero. Did you actually get it to work for you? Appreciate your help. Yes I got it to work, I can email you a little sample workbook if you want? You can send an email to terre08REPLACE1gmailREPLACE2com replace REPLACE1 with @ and REPLACE2 with a period and I will send you a little sample -- Regards, Peo Sjoblom |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using the function DSUM
"Peo Sjoblom" wrote in message
... Are you somehow trying to get all results in one fell swoop, Yes, that was what I got before. The worksheet with an old database shows precisely that. The first cell shows the formula, =DSUM(Database,"Dividend",C869:D870) whereas every cell below it shows {=TABLE(,C870)}. Must have been entered as for an array because it won't allow me to edit it. Hence, I want to start everything all over again but have forgotten how the DSUM formula was entered down the column. Just copying it down won't work. Table is a function that was introduced in either excel 2002 or 2003, it has been removed in 2007 But I'm still using Excel 2003. Can't understand why it now doesn't work. My original worksheet where DSUM was used was with Excel earlier than 2002. I have just tried your suggestion using SUMIF, replacing Dividend_Range with a column of figures for H2 and got all zeros in column I. Thought perhaps changing $A$4:$A$50 to A4:A50 for the Company_Range might work but again they all turned out to be zero. Did you actually get it to work for you? Appreciate your help. Yes I got it to work, I can email you a little sample workbook if you want? That will be great. Shall test it with Excel 2003 and let you know. Thanks. Regards, Tom |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using the function DSUM
Table is a function that was introduced in either excel 2002 or 2003, it
has been removed in 2007 But I'm still using Excel 2003. Can't understand why it now doesn't work. My original worksheet where DSUM was used was with Excel earlier than 2002. I have just tried your suggestion using SUMIF, replacing Dividend_Range with a column of figures for H2 and got all zeros in column I. Thought perhaps changing $A$4:$A$50 to A4:A50 for the Company_Range might work but again they all turned out to be zero. Did you actually get it to work for you? Appreciate your help. Yes I got it to work, I can email you a little sample workbook if you want? That will be great. Shall test it with Excel 2003 and let you know. Thanks. Is the yahoo address you are using here your real email address? Anyway, I sent a sample to that address -- Regards, Peo Sjoblom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DSUM function | Excel Worksheet Functions | |||
DSUM - Modifying the function... | Excel Worksheet Functions | |||
Still a problem with DSUM function | Excel Worksheet Functions | |||
Prom with the DSUM function | Excel Worksheet Functions | |||
DSum function | Excel Worksheet Functions |