DSUM or similar function

[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

 

[Frank Kabel]

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

 

[Stu]

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

 

[Frank Kabel]

Hi
for the second question I would use Filter ('Data - Filter - Advanced
Filter)

 

[Harlan Grove]

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.

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.

Autofilters would be the simplest way to do this.