Using the function DSUM

[Tom]

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]

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

 

[Tom]

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

 

[Peo Sjoblom]

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 tools>options>view 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]

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 tools>options>view 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",C869870) 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

 

[Peo Sjoblom]

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",C869870) 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.

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]

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.

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]

Table is a function that was introduced in either excel 2002 or 2003, it

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.

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