DSUM or similar function

  • Thread starter Thread starter Stu
  • Start date Start date
S

Stu

I have a copy tracking spreadsheet that has a column
(field) for cost and a column (criteria) for cost code. I
have successful set up the DSUM function in order to sort
the costs by the cost code criteria, and total them into
a nominated cell.

Is there a way I can gather a list of cells that has
notes in them (instead of cost amounts), and have them
automatiaclly placed in a list, one note after the other
in a nominated location, instead of totalling them into
one cell as I have with Cost amounts.

Thanks

Stu
 
Hi
not really sure what you're trying to do. could you give
an example (plain text - no attachment please)
 
Hi Frank,

Part of the spreadsheet (which may be 4000 rows) is as
follows:
A B C D
(not part of database)
COST CODE CODE
$45 3 1 cell D2 contains DSUM
$29 2 CODE
$376 6 2
$56 2 .....
$78 1
$99 1
........

The DSUM function, =DSUM(Database,field,criteria), in
cell D2 is as follows:
=DSUM(A1:B4000,"COST",C1:C2), this then creates the
function that adds all the COST amounts that fit the
criteria (in this case 1) and totals them in cell D2 (in
this case $78 + $99 = $177), cell D4 totals all the COST
amounts that fit criteria CODE 2 (=$85). I repeat the
same DSUM to get totals for all the cost codes. This
works well.

The next thing I want to do is as follows:
...... .....
COST CODE CODE
rain delay vary cell D25 DSUM
extra bricks vary CODE
paint change vary delay
......

cell D25 =DSUM(A1:B4000,"COST",C24:C25). The criteria
would then be "vary" and I want it to gather all the
items from the COST column (field) that match that
criteria (in this case "extra bricks" & "paint change")
and list them in one location or column of cells. I guess
I'm after a method to gather all information that matches
a certain criteria, and list it in one place as a
summary. It doesn't matter if the layout or function is
different, but I do need to keep the COST column (field)
and CODE column (criteria) in pretty much the same layout.

Hope this makes sense. Thanks
 
Part of the spreadsheet (which may be 4000 rows) is as
follows:
A B C D
(not part of database)
COST CODE CODE
$45 3 1 cell D2 contains DSUM
$29 2 CODE
$376 6 2
$56 2 .....
$78 1
$99 1
.......

The DSUM function, =DSUM(Database,field,criteria), in
cell D2 is as follows:
=DSUM(A1:B4000,"COST",C1:C2), this then creates the
function that adds all the COST amounts that fit the
criteria (in this case 1) and totals them in cell D2 (in
this case $78 + $99 = $177), cell D4 totals all the COST
amounts that fit criteria CODE 2 (=$85). I repeat the
same DSUM to get totals for all the cost codes. This
works well.
Click to expand...

It may work well, but =SUMIF($B$2:$B$4000,C2,$A$2:$A$4000) would arguably work
even better.

As an alternative, You could copy B1 and paste into E1, select A1:B4000, run
Data > Filter > Advanced Filter, set it to copy the results elsewhere, use *NO*
criteria range (the field in the dialog should be blank), set the output range
as just E1, check the unique records only checkbox, then click OK. This will
fill col E with the distinct code values. Then enter the formula
=DSUM(A1:B4000,"COST",C1:C2) in F1, select the range spanning cols E and F and
all distinct codes in col E, run Data > Table, enter C2 as the Column Input Cell
and click OK. This should fill col F with total costs corresponding to the codes
in col E.
The next thing I want to do is as follows:
..... .....
COST CODE CODE
rain delay vary cell D25 DSUM
extra bricks vary CODE
paint change vary delay
.....

cell D25 =DSUM(A1:B4000,"COST",C24:C25). The criteria
would then be "vary" and I want it to gather all the
items from the COST column (field) that match that
criteria (in this case "extra bricks" & "paint change")
and list them in one location or column of cells. I guess
I'm after a method to gather all information that matches
a certain criteria, and list it in one place as a
summary. It doesn't matter if the layout or function is
different, but I do need to keep the COST column (field)
and CODE column (criteria) in pretty much the same layout.
Click to expand...

Autofilters would be the simplest way to do this.
 
Back
Top