DSUM function

[Kimmer]

Does anyone know anything about this function? I have been everywhere. I
can not get it to work. I have tried every formula I can figure out. I even
went to the library for the Excel 2003 for Dummies book and they dont even
talk about this function. This is part of my homework and if I could just get
this figured out without getting #VALUE error or just a 0 or more than what
the total should be I would be home free and be able to finish this class.

Thanks.

 

[Gord Dibben]

I don't profess to know anything else than what I read in Excel help on DSUM so
may not be able to assist much.

What part of the Function is giving you the problem?

Have you tried the examples from Excel help on DSUM?

How do your answers differ from those in help on DSUM?


Gord Dibben MS Excel MVP

 

[Kimmer]

I am doing this for school and the example they give in book does not help
and I have been to every site there is plus books looking for help.
Book says: In the range E4:E7, use the DSUM function to calculate the total
number of vacation days remaining.
This is my Employee Data worksheet
A5 B5 C5 D5 E5 F5
G5 H5 I5
Name Status Yrs Empl Vaca Used vac Remaining Family Used Fam
Rema
leave leave vac leave
Leave Leave Fam L
Abba FT 0.1 7 5 2
3 0 3
and so on down the list of 109 names

My Leave Summary worksheet:
B3 C3 D3
E3
Vacat Leave Number Eligible Total Days Days Remaining
17-Day 36 612
12-day 29 348
7 day 6 42
none 31 0
Total 102

I tried this formula and I get 698 and the total should be like 456

=DSUM('Employee Data'!A5:I109,"Remaining Vacation Leave",D3:E7)

I have tried others doing B3:E7 for criteria and get 0 for the total. You
have to do this for each vacation leave day. I cant get it to give me the
right answer for the first one let alone any of the others.
Thank you in advance if you have any ideas

 

[Debra Dalgleish]

The DSUM structure is:
=DSUM(table_location,column_to_sum,criteria_range)

The first argument in your DSUM formula should give the location of the
entire table where the data is stored, not just the column you're trying
to sum.
If the table is on the Employee Data sheet, starts in cell A6 and ends
in cell E109, the table's range would be: 'Employee Data'!A6:E109

The second argument in the DSUM formula should give the heading or
number of the column that you want to sum. Since you want to sum the
amounts in column E, you could enter a 5 for that part of the formula.

The third argument gives the location where you have entered a heading
and the criteria for which rows to sum. Perhaps column C has the heading
Vac Days, and you want to sum the records which have 17 vacation days.
To create a criteria range, go to cell D3 on the Employee Data sheet,
and type Vac Days.
In cell D4, type the number of days vacation: 17
In your DSUM formula, you'll refer to cells D3 and D4, which is the
criteria range.

Your revised formula would be:
=DSUM('Employee Data'!$A$6:$E$109,5,'Employee Data'!D34)

 

[Kimmer]

I tried that and got a #NAME error. I sent another response to the guy who
answered me before with more detail in it. Maybe if you look at that post it
will help you help me.

thank you in advance.

 

[Gord Dibben]

The news reader has messed up your data layout considerably so can't tell what
data is in which columns.

You can post the workbook at one of the hosting sites below.

http://www.freefilehosting.net/
http://savefile.com/

When you have uploaded to that site, post the URL so we could download should we
choose.


Gord

 

[Kimmer]

Leave2.xls

 

[Kimmer]

I forgot to thank you.

 

[T. Valko]

The D functions are rather ______ and you'll see why in a second.....

I gave your data on sheet Employee Data A5:I107 the defined name of Table.

I entered this data on sheet Leave Summary as the criteria range to be used
by the DSUM function:

Leave Summary:

B1111 = Vacation Leave, Vacation Leave, Vacation Leave
B1212 = 17, 12, 7

I entered this formula on sheet Leave Summary in cell E4 and copied down to
E6:

=DSUM(Table,6,INDEX(B$11$12,,ROWS(E$4:E4)))

These are the correct results:

17 - 456
12 - 238
7 - 23


--
Biff
Microsoft Excel MVP

Leave2.xls

--
Too old to be in college

The news reader has messed up your data layout considerably so can't tell
what
data is in which columns.

You can post the workbook at one of the hosting sites below.

http://www.freefilehosting.net/
http://savefile.com/

When you have uploaded to that site, post the URL so we could download
should we
choose.


Gord

 

[Kimmer]

thank you very much. Yes these are a _____. I could not find anything
anywhere explaining them well enough. Our book has one example and does not
go into any detail about it.
thanks again.
KIM
--
Too old to be in college

The D functions are rather ______ and you'll see why in a second.....

I gave your data on sheet Employee Data A5:I107 the defined name of Table.

I entered this data on sheet Leave Summary as the criteria range to be used
by the DSUM function:

Leave Summary:

B1111 = Vacation Leave, Vacation Leave, Vacation Leave
B1212 = 17, 12, 7

I entered this formula on sheet Leave Summary in cell E4 and copied down to
E6:

=DSUM(Table,6,INDEX(B$11$12,,ROWS(E$4:E4)))

These are the correct results:

17 - 456
12 - 238
7 - 23


--
Biff
Microsoft Excel MVP

Leave2.xls

--
Too old to be in college

The news reader has messed up your data layout considerably so can't tell
what
data is in which columns.

You can post the workbook at one of the hosting sites below.

http://www.freefilehosting.net/
http://savefile.com/

When you have uploaded to that site, post the URL so we could download
should we
choose.


Gord


On Sat, 19 Apr 2008 12:47:00 -0700, Kimmer
<[email protected]>
wrote:

I am doing this for school and the example they give in book does not
help
and I have been to every site there is plus books looking for help.
Book says: In the range E4:E7, use the DSUM function to calculate the
total
number of vacation days remaining.
This is my Employee Data worksheet
A5 B5 C5 D5 E5 F5
G5 H5 I5
Name Status Yrs Empl Vaca Used vac Remaining Family Used
Fam
Rema
leave leave vac leave
Leave Leave Fam L
Abba FT 0.1 7 5 2
3 0 3
and so on down the list of 109 names

My Leave Summary worksheet:
B3 C3 D3
E3
Vacat Leave Number Eligible Total Days Days Remaining
17-Day 36 612
12-day 29 348
7 day 6 42
none 31 0
Total 102

I tried this formula and I get 698 and the total should be like 456

=DSUM('Employee Data'!A5:I109,"Remaining Vacation Leave",D3:E7)

I have tried others doing B3:E7 for criteria and get 0 for the total.
You
have to do this for each vacation leave day. I cant get it to give me
the
right answer for the first one let alone any of the others.
Thank you in advance if you have any ideas

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

thank you very much. Yes these are a _____. I could not find anything
anywhere explaining them well enough. Our book has one example and does
not
go into any detail about it.
thanks again.
KIM
--
Too old to be in college

The D functions are rather ______ and you'll see why in a second.....

I gave your data on sheet Employee Data A5:I107 the defined name of
Table.

I entered this data on sheet Leave Summary as the criteria range to be
used
by the DSUM function:

Leave Summary:

B1111 = Vacation Leave, Vacation Leave, Vacation Leave
B1212 = 17, 12, 7

I entered this formula on sheet Leave Summary in cell E4 and copied down
to
E6:

=DSUM(Table,6,INDEX(B$11$12,,ROWS(E$4:E4)))

These are the correct results:

17 - 456
12 - 238
7 - 23


--
Biff
Microsoft Excel MVP

Leave2.xls

--
Too old to be in college


:

The news reader has messed up your data layout considerably so can't
tell
what
data is in which columns.

You can post the workbook at one of the hosting sites below.

http://www.freefilehosting.net/
http://savefile.com/

When you have uploaded to that site, post the URL so we could download
should we
choose.


Gord


On Sat, 19 Apr 2008 12:47:00 -0700, Kimmer
<[email protected]>
wrote:

I am doing this for school and the example they give in book does not
help
and I have been to every site there is plus books looking for help.
Book says: In the range E4:E7, use the DSUM function to calculate the
total
number of vacation days remaining.
This is my Employee Data worksheet
A5 B5 C5 D5 E5 F5
G5 H5 I5
Name Status Yrs Empl Vaca Used vac Remaining Family
Used
Fam
Rema
leave leave vac leave
Leave Leave Fam L
Abba FT 0.1 7 5 2
3 0 3
and so on down the list of 109 names

My Leave Summary worksheet:
B3 C3 D3
E3
Vacat Leave Number Eligible Total Days Days
Remaining
17-Day 36 612
12-day 29 348
7 day 6 42
none 31 0
Total 102

I tried this formula and I get 698 and the total should be like 456

=DSUM('Employee Data'!A5:I109,"Remaining Vacation Leave",D3:E7)

I have tried others doing B3:E7 for criteria and get 0 for the total.
You
have to do this for each vacation leave day. I cant get it to give
me
the
right answer for the first one let alone any of the others.
Thank you in advance if you have any ideas

 

[Kimmer]

That worked and I am done with the class. Thank you
--
Too old to be in college

You're welcome!

--
Biff
Microsoft Excel MVP

thank you very much. Yes these are a _____. I could not find anything
anywhere explaining them well enough. Our book has one example and does
not
go into any detail about it.
thanks again.
KIM
--
Too old to be in college

The D functions are rather ______ and you'll see why in a second.....

I gave your data on sheet Employee Data A5:I107 the defined name of
Table.

I entered this data on sheet Leave Summary as the criteria range to be
used
by the DSUM function:

Leave Summary:

B1111 = Vacation Leave, Vacation Leave, Vacation Leave
B1212 = 17, 12, 7

I entered this formula on sheet Leave Summary in cell E4 and copied down
to
E6:

=DSUM(Table,6,INDEX(B$11$12,,ROWS(E$4:E4)))

These are the correct results:

17 - 456
12 - 238
7 - 23


--
Biff
Microsoft Excel MVP



Leave2.xls

--
Too old to be in college


:

The news reader has messed up your data layout considerably so can't
tell
what
data is in which columns.

You can post the workbook at one of the hosting sites below.

http://www.freefilehosting.net/
http://savefile.com/

When you have uploaded to that site, post the URL so we could download
should we
choose.


Gord


On Sat, 19 Apr 2008 12:47:00 -0700, Kimmer
<[email protected]>
wrote:

I am doing this for school and the example they give in book does not
help
and I have been to every site there is plus books looking for help.
Book says: In the range E4:E7, use the DSUM function to calculate the
total
number of vacation days remaining.
This is my Employee Data worksheet
A5 B5 C5 D5 E5 F5
G5 H5 I5
Name Status Yrs Empl Vaca Used vac Remaining Family
Used
Fam
Rema
leave leave vac leave
Leave Leave Fam L
Abba FT 0.1 7 5 2
3 0 3
and so on down the list of 109 names

My Leave Summary worksheet:
B3 C3 D3
E3
Vacat Leave Number Eligible Total Days Days
Remaining
17-Day 36 612
12-day 29 348
7 day 6 42
none 31 0
Total 102

I tried this formula and I get 698 and the total should be like 456

=DSUM('Employee Data'!A5:I109,"Remaining Vacation Leave",D3:E7)

I have tried others doing B3:E7 for criteria and get 0 for the total.
You
have to do this for each vacation leave day. I cant get it to give
me
the
right answer for the first one let alone any of the others.
Thank you in advance if you have any ideas

 

[T. Valko]

In the real world, that could've been done with a very simple SUMIF formula.

--
Biff
Microsoft Excel MVP

That worked and I am done with the class. Thank you
--
Too old to be in college

You're welcome!

--
Biff
Microsoft Excel MVP

thank you very much. Yes these are a _____. I could not find anything
anywhere explaining them well enough. Our book has one example and
does
not
go into any detail about it.
thanks again.
KIM
--
Too old to be in college


:

The D functions are rather ______ and you'll see why in a second.....

I gave your data on sheet Employee Data A5:I107 the defined name of
Table.

I entered this data on sheet Leave Summary as the criteria range to be
used
by the DSUM function:

Leave Summary:

B1111 = Vacation Leave, Vacation Leave, Vacation Leave
B1212 = 17, 12, 7

I entered this formula on sheet Leave Summary in cell E4 and copied
down
to
E6:

=DSUM(Table,6,INDEX(B$11$12,,ROWS(E$4:E4)))

These are the correct results:

17 - 456
12 - 238
7 - 23


--
Biff
Microsoft Excel MVP



Leave2.xls

--
Too old to be in college


:

The news reader has messed up your data layout considerably so
can't
tell
what
data is in which columns.

You can post the workbook at one of the hosting sites below.

http://www.freefilehosting.net/
http://savefile.com/

When you have uploaded to that site, post the URL so we could
download
should we
choose.


Gord


On Sat, 19 Apr 2008 12:47:00 -0700, Kimmer
<[email protected]>
wrote:

I am doing this for school and the example they give in book does
not
help
and I have been to every site there is plus books looking for
help.
Book says: In the range E4:E7, use the DSUM function to calculate
the
total
number of vacation days remaining.
This is my Employee Data worksheet
A5 B5 C5 D5 E5 F5
G5 H5 I5
Name Status Yrs Empl Vaca Used vac Remaining Family
Used
Fam
Rema
leave leave vac
leave
Leave Leave Fam L
Abba FT 0.1 7 5 2
3 0 3
and so on down the list of 109 names

My Leave Summary worksheet:
B3 C3 D3
E3
Vacat Leave Number Eligible Total Days Days
Remaining
17-Day 36 612
12-day 29 348
7 day 6 42
none 31 0
Total 102

I tried this formula and I get 698 and the total should be like
456

=DSUM('Employee Data'!A5:I109,"Remaining Vacation Leave",D3:E7)

I have tried others doing B3:E7 for criteria and get 0 for the
total.
You
have to do this for each vacation leave day. I cant get it to
give
me
the
right answer for the first one let alone any of the others.
Thank you in advance if you have any ideas

 

[Gord Dibben]

Thanks from me also for your help on this Biff.

I was as lost as Kimmer on DSUM


Gord