ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Using the function DSUM (https://www.excelbanter.com/excel-discussion-misc-queries/159371-using-function-dsum.html)

Tom

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



Peo Sjoblom

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





Tom

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







Peo Sjoblom

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









Tom

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











Peo Sjoblom

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




Tom

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



Peo Sjoblom

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





All times are GMT +1. The time now is 08:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com