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

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)

Whe
$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


"Debra Lisa" wrote:

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

Hi again Debra,

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

Regards,

OssieMac

"OssieMac" wrote:

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)

Whe
$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


"Debra Lisa" wrote:

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

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


"OssieMac" wrote:

Hi again Debra,

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

Regards,

OssieMac

"OssieMac" wrote:

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)

Whe
$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


"Debra Lisa" wrote:

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

=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



"Debra Lisa" wrote in message
...
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


"OssieMac" wrote:

Hi again Debra,

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

Regards,

OssieMac

"OssieMac" wrote:

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)

Whe
$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


"Debra Lisa" wrote:

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

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.

Ill 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.
"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

"Debra Lisa" wrote:

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


"OssieMac" wrote:

Hi again Debra,

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

Regards,

OssieMac

"OssieMac" wrote:

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)

Whe
$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


"Debra Lisa" wrote:

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

Why are you using 4 separate formulas?




--

Regards,

Peo Sjoblom

"OssieMac" wrote in message
...
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.
"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

"Debra Lisa" wrote:

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


"OssieMac" wrote:

Hi again Debra,

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

Regards,

OssieMac

"OssieMac" wrote:

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)

Whe
$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


"Debra Lisa" wrote:

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