Excel - DSUM function

Asked By DebraLis on 27-Aug-07 06:00 PM
Need to use the DSUM function to calculate the total number of vacation days
remaining. Please help, I have been stumped for 2 days now.

Leave Summary

Vacation Leave	Number Eligible	Total Days	Days Remaining
17-day	36	612
12-day	29	348
7-day	6	42
none	31	0
Total	102	1002

the database is:

Name	Status	Yrs Employed	Vacation Leave	Used Vacation Leave	Remaining
Vacation Leave
Abba	FT	0.1	7	5	2
Abbot	FT	4.0	12	7	5
Adtkins	FT	5.7	17	4	13
Andrews	FT	8.8	17	3	14
Baker	PT	2.5	0	0	0
Berg	FT	4.5	12	4	8
Bergeman	FT	10.0	17	4	13
Blake	CN	0.3	0	0	0



Please help.

--
DLLewis




OssieMa replied on 27-Aug-07 09:36 PM
Hi Debra,

Is a different function acceptable? If so try the following:-

In the Vacation leave column instead of using text values of 17-day etc;
custom format these cells using:-
Number format->number->custom and insert 0"-day" as the format.

Then simply center the numbers (17,12,7 and use 0 for none). The display
will have the '-day' appended to it but you can then use the actual values of
17,12,7 and 0 in formulas.

Formula for Days Remaining:-

=SUMIF($D$2:$D$100,H2,$F$2:$F$100)

Where:
$D$2:$D$100 is from Vacation Leave column in Database

H2 is from the column containing Vacation Leave in the summary

$F$2:$F$100 is from the Remaining Vacation leave column in database.

Hope it helps,

Regards,

OssieMac
OssieMa replied on 27-Aug-07 09:44 PM
Hi again Debra,

Third paragraph should read Then simply ENTER the numbers not center.

Regards,

OssieMac
DebraLis replied on 28-Aug-07 05:18 AM
Thanks for the info, but I am required to used the DSUM function.
I am a CIS student and this was a review assingment and I am missing the
concept.
Thanks anyway.
DLLewis
Peo Sjoblom replied on 28-Aug-07 10:36 AM
=DSUM(Database,"Remaining Vacation Leave",F1:F2)


where F1 holds the header Remaining Vacation Leave and F2 is blank, if you
need other criteria just add a headers in the same order as in the database
and see help for further instructions


--
Regards,

Peo Sjoblom
OssieMa replied on 28-Aug-07 06:08 PM
Hi again Debra,

Can still help you but it is not the method that I would use because you
have to continually alter criteria data on the worksheet or insert criteria
on the worksheet in multiple places.

I’ll try to give you a detailed explanation of the method of entering the
criteria in multiple places.

I have the summary data you gave me in range A4:D9 (Including the column
headers and totals). The ‘Days Remaining’ being in column D. There are 3
blank rows above the summary.

The Database example is in range D12:F20. (Column headers in row 12)

To build the 4 criteria in the blank rows above the summary:-
In the 4 cells A1 to D1 insert ‘Vacation Leave’ as column headers. (Same
name 4 times)
In A2 insert 17
In B2 insert 12
In C2 insert 7
In D2 insert 0

Cells D5 to D8 are the data cells under Days Remaining in the summary. In
these cells insert the following formulas:-
D5 Insert =DSUM($A$12:$F$20,"Remaining Vacation Leave",A1:A2)
D6 insert =DSUM($A$12:$F$20,"Remaining Vacation Leave",B1:B2)
D7 insert =DSUM($A$12:$F$20,"Remaining Vacation Leave",C1:C2)
D8 insert =DSUM($A$12:$F$20,"Remaining Vacation Leave",D1:D2)

Now an explanation of the formula. Explanation based on D5 formula.
The range $A$12:$F$20 is the full range of the database including the column
headers.
containing the data to be summed.
A1:A2 is the criteria range. A1 contains the name of the column header in
the database under which it should find the value which is in A2.
It then sums the values in Remaining Vacation Leave where the criteria in
Vacation Leave column matches.

I hope it helps,

Regards,

OssieMac
Peo Sjoblom replied on 28-Aug-07 11:19 PM
Why are you using 4 separate formulas?




--

Regards,

Peo Sjoblom
OssieMa replied on 29-Aug-07 12:32 AM
Either that or change the value in the criteria each time you want a new
criteria. Perhaps I am missing something. Pls feel free to show a better way
because that is why I like these forums; I continually learn new ways of
doing things. Personally I like the sumif method I described in my earlier
post on this.

Regards,

OssieMac
Peo Sjoblom replied on 29-Aug-07 10:24 AM
The reason I asked was that I can't really see the OP's layout, in OE it
came out as warped.
However if it is the way you interpret it then DSUM should not be used since
it doesn't make any sense unless you can do it with one formula thus your
first approach seems more appropriate.. OTOH having read the OP's answer to
your first post again it seems that this is homework, nuff said.
Otherwise DSUM can do anything and more a SUMIF formula can do but the
criteria setup is archaic to say the least but it is good to learn because
the advanced filter uses the same type of criteria





--
Regards,

Peo Sjoblom