DSUM function

[Debra Lisa]

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.

 

[OssieMac]

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

 

[OssieMac]

Hi again Debra,

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

Regards,

OssieMac

 

[Debra Lisa]

Thanks for the info, but I'm required to used the DSUM function.
I'm a CIS student and this was a review assingment and I'm missing the
concept.
Thanks anyway.
DLLewis

 

[Peo Sjoblom]

=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

 

[OssieMac]

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 A49 (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",D12)

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.
"Remaining Vacation Leave" is the column header name of the column
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]

Why are you using 4 separate formulas?

 

[OssieMac]

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]

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