Hi

Then take a look at the Sumproduct function.
Something like
=SUMPRODUCT(($A$2:$A$100="criteria1")*($B$2:$B$100 ="criteria2")*$C2:$C100)
you can change your criteria on each row, or have the criteria located
in other cells.

for a great explanation on how Sumproduct works, take a look at
http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards

Roger Govier


"Nick''''s brother" wrote in
message ...
Thank you for the post Roger. I'm afraid I didn't make my particular
question clear enough. The reason I want to copy the DSUM formula
down a
column is I would like a "dynamic" criteria list that gives me a
distinct
DSUM result based on the criteria values located within the row of
each DSUM
formula. Let's say that the data being evaluated by DSUM is in
another sheet
or tab.

Sort of like having a list of say 100 query requests to evaluate
against the
data, where each of the 100 lines has the input variables or filters
used in
each separate query. This would leverage the functionality of both a
spreadsheet and a database at the same time.

Thanks,
Nick's brother

"Roger Govier" wrote:

Hi

The DSUM formula should not need to be copied down.
You should have the field names on Row 1, followed by any number of
blank rows in which to enter your criteria.
Then the field names should be repeated, say on row 5, with all of
you
data from row 6 say to row 100.

Suppose you have columns for Name, Category and Amount
if in A2 you entered ="=Fred"

then the formula
=DSUM(A5:C100,C1,A1:C2)
would add the Amounts for all rows where column A = Fred
If you also wanted the results for Fred and Jim, you would enter in
A3
="=Jim" and amend the formula to
=DSUM(A5:C100,C1,A1:C3)

If you wanted the Amounts for Fred or Jim but only for Category A,
then
inserting in B2 ="=A"
would achieve this with the same formula.

If you wanted to have an "OR" situation, then you would need to add a
farther column heading in D1 repeating Category as the heading, and
entering ="=B" in D2
Change the formula to
=DSUM(A5:C100,C1,A1:D3)
and you would nor have the amounts where name - Jim and name = Fred
and
Category = A or B.

--
Regards

Roger Govier


"Nick''s brother" wrote in
message ...
Hi, this is my first time. Sorry if this has been asked and
answered
already
but I couldn't find an answer to this specific question on previous
posts.

I'm trying to use the DSUM function and copy the formula down a
column
of
all rows where my criteria data resides. The field names in row #1
and
records containing criteria starting at row #2. Is there any way
that
DSUM
can do this? The formula obviously works for my first data record
(row 2)
that contains criteria where the criteria argument would be for
example
D1:E2. But when I want to evaluate the criteria on the second data
record I
haven't figured out how to both reference the field names (D$1:E$1)
and the
criteria set for this specific row (D3:E3).

...and then the next record referencing the field names (D$1:E$1)
and
the
criteria (D4:E4) and so on.

I think it would be great to really leverage the functionality of a
spreadsheet with a basic multiple criteria join instead of having
to
use
Access to make this type of join.

Thanks in advance for your suggestions.