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
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,